• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

Conditional Formatting in Excel not working

We have a shared Excel spreadsheet that has conditional formatting on some of the cells.  When I look at the spreadsheet the formatting does not appear however when I do a print preview the formatting does appear.  Any idea of what could be causing this?  We have 1 pc up here that runs on a print server and the formatting looks fine on that pc.  I wasnt sure if this was related to the print server or if it could be an Excel update or add-in that only exists on the one pc.   Currency Formatting shows in Print Preview
0
asb_infotech
Asked:
asb_infotech
  • 12
  • 6
  • 5
  • +2
3 Solutions
 
SiddharthRoutCommented:
May I see a sample file?

Sid
0
 
asb_infotechAuthor Commented:
Here's the file.  Sorry it took so long but I had to trim a lot of the data out of it. Scorecard.xlsx
0
 
SiddharthRoutCommented:
Which column are you talking about?

Sid
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
asb_infotechAuthor Commented:
On the EMPLOYEE tab, look in cell V1.  There should be a drop down box and if you change to different employees the formatting should change to match the formatting specified in Column A  You may have to go through several individuals before you generate the error.  And like I said on one workstation that we have here, it works fine every time.
0
 
SiddharthRoutCommented:
Ah ok.

No even I was experiencing that behavior but when I changed the format of the cells to currency "$" from "General" in the AB1 to AD3 and other cells where the value of Col AE is $ then it was working fine.

Sid
0
 
asb_infotechAuthor Commented:
I've tried that and it still doesnt appear to be working.  I even tried changing the format in the related Data tab where the data pulls from.  The fields appear in the correct format in Columns AA through AE, but in Columns A through N, the format is still not carrying over.  

We do have a workstation up here where the conditional formatting is correct every time.  I wasn't sure if there was a possible difference between the Excel Plug-ins or Service Packs between that workstation and the others that could be contributing to the problem. Conditional Formatting 2
0
 
SiddharthRoutCommented:
Hmm, you are right.

I am requesting more experts to join us.

Sid
0
 
dlmilleCommented:
Your problem is with merged cells.  If you unmerge the cells, the cell formatting works correctly.

Is there really a need to merge these cells?  Why not just make the column wider?

Dave
0
 
dlmilleCommented:
I would just use one column and make it wider and move on.  -

However, to make the cell formatting work correctly, I believe you can achieve this by set the formatting to the right-most cell in the merged cell list, rather than all of the cells in the merged list.  It worked on an ad-hoc basis.

Give me a few moments to change all and test.

Dave
0
 
dlmilleCommented:
The conditional formatting is fairly complex, and with overlapping "applies to" ranges.   It can take some time for me to get this reset correctly.  

I'm starting to think that getting rid of the merged cells, might be the easiest fix.

@asp_infotech - your thoughts?

Dave
0
 
dlmilleCommented:
Nope - doesn't have to do with merged cells - that was a coincidence.  But, I found a lot of overlapping conditional formatting which could be creating conflicts.

Perhaps sharing what your color rules are will help - perhaps deleting the conditional formatting, selecting the ENTIRE RANGE and then putting in a condition on $A8 applies to $H$8:$Q$15 would work...

Still testing...

Dave
0
 
dlmilleCommented:
Ok - We need more help.  I cut out all the conditional formatting but a simple $A8 on $, %, G to apply to range $A8:$Q15 and STILL got inconsistent results.

Corrupt worksheet?  Let me tell you why it COULD be...

The attached is the simplified file, with a worksheet_Calculate event to set Range(H8:Q15).formula = Range(H8:Q15).formula

AND EVERYTHING WORKS FINE.  Except this is WAY too much overhead.  There must be better formatting E-E'ers out there or you have a corrupt worksheet.

While we wait on response from other experts, please consider rebuilding this small sample with only the 3 criteria I mentioned above.  If its consistent from there, you can build the rest and call your sample corrupt.

Cheers,

Dave
Scorecard-r2.xlsm
0
 
rspahitzCommented:
Dave,

With your file I'm not getting the formatting problems.  Can you be more specific about what you mean about inconsistent?
Note that this computer has a Brother HL2040 printer defined.  It certainly doesn't seem like the printer should make EXCEL appear different (only the preview) but I thought maybe someone can try changing the default printer or preview on a different printer to see if it fixes it.

Let me check the author's original file.

0
 
dlmilleCommented:
change the drop down to different employee numbers and then sometimes instead of General formatting "G" you get $ formatting and vise versa...

Dave
0
 
dlmilleCommented:
Oh - and the file I submitted is a "Patch" - turn off the event handler for Sheet_Calculate...
0
 
rspahitzCommented:
OK, on the original file, I discovered something interesting.
In cell A8, I changed it from G to $ and saw no change in "2010" and "Annual Goal".  I then change to % and saw the change.  I undid (Ctrl-Z) and saw the change for the $ as it was supposed to be.  Undid again and everything was restored.  Changed back to $ and nothing.

That led me to believe the there is a refresh problem.  I pressed F9 (recalculate) and it worked fine.

I'm not sure whether you can turn off recalculate for any particular book/sheet.  I thought it was for all of excel.
However, since it's a shared workbook, Excel may have some settings that indicate that it should turn off recalc under some conditions and apparently those conditions are being met.

After a little thought, I realized that the following code could force Excel to properly recalculate when there's a change in column A.
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count = 1 Then
        If Target.Column = 1 Then
            Calculate
        End If
    End If
End Sub

Open in new window


See if that works for you (obviously save the document as an xlsm to save the macro.
Also, experts, if you know any ways to force a recalc without having to press F9 or run this macro, let us know.
0
 
dlmilleCommented:
This won't kick off as part of the drop down.

Also, when I did do a selection change in that area after having gotten a bad format match, the calculate didn't help anything - and I was hoping, but makes sense as Calculate runs every time the dropdown changes as well - if it didn't then my not so great but works patch wouldn't have worked.

Now, if we could force a formula calculate!

to see errors, just iterate through the dropdown list till you see one, then click in column a.

Dave
0
 
dlmilleCommented:
>>Now, if we could force a formula calculate!

I meant conditional format calculate!

lol,

Dave
0
 
rspahitzCommented:
So then maybe we need to change things a bit...add some VBA when selecting the employee.

And maybe I'm missing something, but the only dropdowns I see are the ones for selecting criteria/filtering.  If we add a Formsor Controls Toolbox dropdown on top of it and link it to the cell, we can pick any employee and use the Change event.

Obviously, this shouldn't be necessary, but something is causing Excel not to recalculate.  I suspect that it's just got too many complex calculations and thinks it has exceeded its internal memory limit for optimal performance...this could be a question for the MS team to investigate since they'd have the source code.
0
 
dlmilleCommented:
The drop-down list to change employees in on merged cell V1:W1.  Yea - possibly an active-x control could work, and code behind it to test which row was affected using column A to keep processing down on refreshing the conditional formats.

Dave
0
 
rspahitzCommented:
Hmmm...tried adding a Forms dropdown and it didn't help.
It seems that when you use a dropdown to affect a group of cells that reference a table to pull information, then other cells that reference that group of cells cannot be used to indirectly format other cells.

As we discussed, it seems that using the lookup function turns off the cell_changed event so even though the cell value changes, the conditional formatting doesn't know about it.
 
I'd call it an unintended limitation of Excel.

This could probably all be handled through VBA, so it looks like Dave's simple suggestion of applying the formula back to itself forces Excel to recalculate everything.
0
 
dlmilleCommented:
Darn - I was hoping my proposed solution wasn't THE solution, lol....

Dave
0
 
Rory ArchibaldCommented:
FWIW, it works perfectly for me in 2010, no matter which employee I select.
0
 
asb_infotechAuthor Commented:
We have 2 users up here that it works for them no matter which employee is selected as well.  That's why I wasn't sure if there was an MS update or Excel plug-in that we may only have installed on those 2 PCs.
0
 
SiddharthRoutCommented:
Tested in Excel 2007. Doesn't work for me.

Sid
0
 
Rory ArchibaldCommented:
Just tested in 2007 and doesn't work for me there, so I suspect a bug that they have since fixed. (there are quite a few in CF)
0
 
rspahitzCommented:
Hmmm...yes, at home I'm using 2007 and it failed; at work I'm using 2010 and it's working.  Sounds like a fixed bug when you upgrade.
0
 
dlmilleCommented:
It doesn't work in 2007 for me either.

I assume all the "doesn't work" comments are referring to the base spreadsheet and not my "patch":  http:#a35307541.html

Dave
0
 
SiddharthRoutCommented:
Yeah, I am referring to OP's file :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 12
  • 6
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now