• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting is Not Working

N

Nikki91775

Member
I have some pretty large files that were created back in Excel 2003 with conditional formatting. Here is what the rules say:


J50 = 0, then no formatting

J49 > J50 - Red with white text

J49<=J50 - Green with black text


Where it should be red, it is coming up green. I have deleted it and tried to start over and it still is not working. I believe that may be some things from the file coming over from different versions of Excel, but it is frustrating that it just will not work.
 
Hi Nikki ,


Ensure that the CF rules are in the order you have mentioned , since two rules are in conflict ; when J50 = 0 , it can also be that J49 <= J50 , or J49 > J50 depending on what J49 contains.


Use the MOVE UP or MOVE DOWN buttons to position the CF rules in the exact order you have posted , and check the STOP if TRUE checkbox , so that when one rule is TRUE , it is applied , and the other rules are not tested.


Narayan
 
I have tried that and it is not working. This is the issue I believe and I cannot figure it out. In the formula where I am getting the actual data from - the formula, states, if error than 0. So J50 is coming up zero and the conditional formatting is not working. If I actually have a value like 1 or 2, the conditional formatting works.
 
Hi, Nikki91775!

Could you check if values are numeric and not text with numeric contents? Otherwise consider uploading a sample file.

Regards!
 
Here is part of the report, did not upload all of it.


https://hotfile.com/dl/225526644/6db8957/Bowling_Chart_Example.xls.html
 
Hi, Nikki91775!

Not much more than checking if the value of that cell is entered in it directly and has a text prefix (', " or ^), if it's the result of a formula that might return non numeric values, if it's filled from a macro, or if it's imported from other sources.

Regards!
 
Hi Nikki ,


Verify that the data is really 0 and not a decimal !


Formatting can make anything be displayed as 0.


Narayan
 
And in some cells it is looking at the zero as if it is greater than the number. For example the 3 will turn green because it thinks zero is more than 3, but it should be red.
 
Hi Nikki,


You have placed the zero in IFERROR as text:


Code: 
=IFERROR(VLOOKUP(E48,'C:UsersFawwadAppDataLocalTemp[West MCA 2013.xlsx]SP Pivot'!$B$12:$H$39,7,FALSE)/1000,"0")


..Have you checked that.


Regards,
 
Hi Nikki ,


What is there to see in those cells ? The CF rules you have mentioned are not present in those cells !


The cells themselves have the following formulae :


N38 : =IFERROR(VLOOKUP(E36,'C:DOCUME~1SAGARR~1LOCALS~1Temp[West MCA 2013.xlsx]SP Pivot'!$B$12:$H$39,7,FALSE)/1000,"0")


N46 : =IFERROR(VLOOKUP(E44,'C:DOCUME~1SAGARR~1LOCALS~1Temp[West MCA 2013.xlsx]SP Pivot'!$B$12:$H$39,7,FALSE)/1000,"0")


N50 : =IFERROR(VLOOKUP(E48,'C:DOCUME~1SAGARR~1LOCALS~1Temp[West MCA 2013.xlsx]SP Pivot'!$B$12:$H$39,7,FALSE)/1000,"0")


Since these are external links , I cannot change them and see the effect ; only you can. Replace all the "0" parts ( since "0" is text ) by just 0.


But I am again repeating that changing this will not do anything , since the CF rules for these cells have nothing to do with their containing a zero value. Please check.


Narayan
 
Right, I took the conditional formatting off the cells because it was not working and tried to start over. Yes, let me see if that will work and I will let you know.


Thanks.
 
Hi, Nikki91775!

I agree with Faseeh regarding the zero as text (quoted) and with NARAYANK991 regarding the CF rules present for your posted cells N38, N46 & N50. They only have rules for yellow and green backcolor if cells contain 'è' and 'ì' characters.

Regards!
 
Ok... here is another one where the conditional formatting is not working and this does not have the IFERROR - Look at Level2 worksheet in cells N 40, 41 & 42.
 
Hi, Nikki91775!

Have you read our comments regarding the absence of your referred rules for the previous ranges N38-N-46-N50? No matter if a rule has or not IFERROR function in its formula, or if it sets a numeric or a text value, if there's no CF rule that uses that formula in that range.

So I'd recommend you that:

a) follow Faseeh's indications regarding setting numeric values instead of text

b) check all the cells where you think that CF is not working properly and verify that they actually have CF rules set and then that they're well defined

c) if still doesn't work, please come back and post that file with previously fixed, checked and verified CF rules in the non working cells.

Regards!
 
Hi Nikki ,


Why do you say that the CF rules are not working in N40 and N41 ?


The rules are straightforward :


1. If N42 = 0 , then there is no colour in N40 and N41.


2. If N40 > N42 , then N40 turns RED


3. If N40 <= N42 , then N40 turns GREEN.


A blank cell is the same as 0 ; at present the cells N40 through U41 are all colourless.


Put a value such as 20 in any of the cells N42 through U42 and see what happens ; then put a value such as 75 , and again see what happens.


Narayan
 
Yes, I agree with you, but I want those cells to remain colorless until I put numbers in the actuals and that is my if N42=O than it is blank. I have changed the number in the pivot table to actualy reflect numeric value and still did nothing


Is there a way I can get rid of N42=O and say something like if N42 is blank than do nothing?
 
Hi Nikki ,


I do not know what we are discussing !


I am copying and pasting the range J40:U42 :

[pre]
Code: 
49	45	45	49	49	45	47	47	45	51	42	38
47	43	43	47	47	43	45	45	43	49	40	36
42	31	32	44	0
[/pre]
In the worksheet , J40:M41 is colored RED , while all other cells are colorless ; where is the problem , according to you ?


Narayan
 
You must log in or register to reply here.
Back
Top