Solved

Conditional Formatting in Excel not working

Posted on 2011-03-25
29
540 Views
Last Modified: 2012-05-11
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
Comment
Question by:asb_infotech
  • 12
  • 6
  • 5
  • +2
29 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35217184
May I see a sample file?

Sid
0
 

Author Comment

by:asb_infotech
ID: 35217726
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35217739
Which column are you talking about?

Sid
0
 

Author Comment

by:asb_infotech
ID: 35218212
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35218316
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
 

Author Comment

by:asb_infotech
ID: 35232072
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35307206
Hmm, you are right.

I am requesting more experts to join us.

Sid
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35307301
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35307325
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35307421
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35307474
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35307541
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 35307680
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
 
LVL 41

Expert Comment

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

Dave
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 41

Expert Comment

by:dlmille
ID: 35307697
Oh - and the file I submitted is a "Patch" - turn off the event handler for Sheet_Calculate...
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35307704
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35307821
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35307934
>>Now, if we could force a formula calculate!

I meant conditional format calculate!

lol,

Dave
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 166 total points
ID: 35307975
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
 
LVL 41

Accepted Solution

by:
dlmille earned 167 total points
ID: 35309036
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 35309773
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35310379
Darn - I was hoping my proposed solution wasn't THE solution, lol....

Dave
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35310441
FWIW, it works perfectly for me in 2010, no matter which employee I select.
0
 

Author Comment

by:asb_infotech
ID: 35314387
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35314401
Tested in Excel 2007. Doesn't work for me.

Sid
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 167 total points
ID: 35314881
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 35315121
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
 
LVL 41

Expert Comment

by:dlmille
ID: 35317033
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35317049
Yeah, I am referring to OP's file :)

Sid
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now