Solved Colored if rule under conditonal formatting

July 18, 2012 at 04:45:39
Specs: Windows 7
Look at Conditional Formatting.
1 - From the Ribbon select Home - Styles - Conditional Formatting,
2 - From the drop down select 'Manage Rules' and select 'New Rule'
3 - In the dialog box select the last item in the list 'Use a formula to determine which cells to format'
4 - Enter your formula in the box.
Something like =IF(A1>B1,TRUE,FALSE)
5 - Note that TRUE and FALSE do not have double quotes - they are Excel's logical values true and false.
6 - Click the format button and from the 'Fill' Tab select a color
7 - OK your way out.


Hi,

I found this solution to my question on your forum, how do I use this on multiple cells without having to create 3 rules per row? As you mentioned in that post... excel inserts the $ sign. I tried to remove this but everytime I click on apply excel inserts it again.
Can someone help? Using 2007.


See More: Colored if rule under conditonal formatting

Report •

#1
July 18, 2012 at 04:50:56
By the way...what I'm trying to do is indicate bny color if a cell increases or decreases from the value in the previous cell. SO if B1>A1 it should e green, if B1=A1 it should be orange and if B1<A1 it should be red.

Report •

#2
July 18, 2012 at 10:23:09
✔ Best Answer
Two items:

First:

=IF(A1>B1,TRUE,FALSE)

can be reduced to:

=A1>B1

If you were to put that shortened formula in a cell, it would return either TRUE or FALSE based on the values in the cells.

Since CF is looking for either TRUE or FALSE, you don't need to force the return of those logical values with an IF function. They will occur "naturally" and CF will be quite happy to use them. Keeping that in mind, you will save a lot of keystrokes when writing CF rules.

Second:

When you Select multiple cells and apply CF Rules, the rules are applied individually to each cell.

e.g. If you:

1 - Select B1:B3
2 - Use =B1>A1 as your 1st rule
3 - Format as Green
4 - Use =B1=A1 as your 2nd rule
5 - Format as Orange
etc.

B1 will be formatted based on the value in A1
B2 will be formatted based on the value in A2
B3 will be formatted based on the value in A3

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


Report •

#3
July 19, 2012 at 00:33:57
Thanks so much...I got it!

Report •

Related Solutions

#4
July 19, 2012 at 01:01:19
Just one more thing...Is there a way that I can always mark a value of 100 as green? Cause it cannot exceed 100% so if I reach 100 I want it to show as green. Currently if it's 100 for more than one month it will show as orange because of the formula =C4=B4 format orange.

Report •

#5
July 19, 2012 at 04:32:37
If I understand you correctly, this should work

=OR(B1>A1,B1=100)
Format as green

As long as either condition is TRUE, an OR function will return TRUE.

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


Report •

#6
July 20, 2012 at 00:14:04
Thank you I got it...

Just a note...make sure that this green formula is the first in line... otherwise it will still color it orange as there is a tick box selected that says "Stop if true" on CF under your rules.

Thanks so much for all the help!


Report •

Ask Question