Conditional Formating comparing 2 cells

April 26, 2011 at 12:17:35
Specs: Windows XP
I have a report that compares monthly %s. I look to see if there is a difference between the months. If difference is 10% greater or less I look to find out why. My spread sheet looks like this:
A B C D E F G H I J K
Description Jan Feb % Diff Mar % Diff Arp % Diff May % Diff

My goal is to hide the % Diff columns (D,F,H,J), so you only see the months. I want to conditional format the cells for each month to change colors if it’s 10% higher/lower compared to the previous month. If it is 10% higher or more, change color to blue, if 10% or lower change color to red. I have a formula in all the % Diff columns to see if they are 10% higher/lower compared to the previous month. I am not sure if I am entering the formula correct on the conditional formatting under the “Use a formula to determine which cells to format.” I have selected C2 to enter the formula and have the following formulas entered =(IFD2>$L$1,TRUE,FALSE) formatted to change to blue & =(IFD2<$M$1,TRUE,FALSE) formatted to change to red. (L1=.1 & M1=-.1) But when I hit ok to apply the conditional formatting I get a warning that says “the formula contains an error” I am not too sure how to fix it. Can you lend a hand?


See More: Conditional Formating comparing 2 cells

Report •


#1
April 26, 2011 at 12:26:48
Try this:

=(IFD2>$L$1,TRUE,FALSE) < yours

=IF(D2>$L$1,TRUE,FALSE) < mine

see the difference?

MIKE

http://www.skeptic.com/


Report •

#2
April 26, 2011 at 12:32:34
I how did I not see that!!! I guess I looked at it too long. Thanks. I bet this will be the easiest one you answer all day.

Report •

#3
April 26, 2011 at 12:34:08
=IF(D2>$L$1,TRUE,FALSE) < Mike's

=D2>$L$1 < mine

see the difference? ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 26, 2011 at 12:34:40
Sometimes it just takes a new pair of eyes.

Glad I could help.

MIKE

http://www.skeptic.com/


Report •

#5
April 26, 2011 at 12:40:27
DerbyDad03,

I was trying not to confuse, but you are correct,
Conditional Formatting needs only for the formula to return a TRUE response.

So while both will work, yours is the shorter. ;-)

MIKE

http://www.skeptic.com/


Report •

#6
April 26, 2011 at 12:49:23
Sometimes I just can't resist a "teaching moment". ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
April 26, 2011 at 12:59:22
Thanks!! More info!!

Report •


Ask Question