Excel Conditional Formatting

Anyone good with this stuff?

I'm trying to make the cells in a column turn red, green, or yellow based on other cells.

For example, I want the cell to turn red if Cell A is -30% or lower, or if Cell B is < 12, or if Cell C is a negative value, etc.

It should be easily found on the menu bar no?

Which version of Excel? Do you want the cell to change based on its own value or based on another cell? Do you want it based on the same cell for all cells where you want the change or based on same row/different column, or same column/different row?

Lots of questions to be answered.

P.S. I *am* good with this stuff.

Excel has a pretty good built-in tool for conditional formatting, but I don't know how to make the value in one cell affect the formatting in a different cell, if that's what you're going for.

Hmmmm... you're looking for conditional formatting using three different values under three different conditions. You should be able to use a formula that evaluates those cells based on your conditions to determine the resulting colour of the cell you are setting this up in. The formulae would be a series of OR evaluations for each of your three tests.

Here is what I am working with:



I want the column Financial Status to be a certain colour based on the other cells.

H is Balance October 31, K is %change, P is months until 0 funds

I want it to show up green if all three statements are true: H > 0, K > 0%, P > 24

Yellow if H > 0, K is less than 0% but greater than -30%, P is between 12 and 24

Red if H<0, K less than -30%, P less than 12

Sorry, I did the above when I thought it was an "OR" of the three conditions. Just change the formula to AND.

How you get to the rule editing depends a lot on the version of Excel you have but, essentially, configure it to use a formula and use the one I've got (changed to AND):

Code:


=AND($A2<0.3,$B2<12,$C2<0)


The dollar sign in front of the A means to lock-in the column which is checked. That formula was entered (originally) for a cell in row 2, so the $A2 means that if I copy the formatting to a cell in row 4, the formula will change to check $A4. The conditional formatting is applied to the cells in column D.

I assume if it doesn't match any of the three rules then it's no cell filling? Then you need three rules.

rule 1 -- to set green:

Code:


=AND($H2>0,$K2>0,$P2>24)


rule 2 -- to set yellow:

Code:


=AND($H2>0,AND($K2<0,$K2>-0.3),AND($P2>=12,$P2<=24))


rule 3 -- to set red:

Code:


=AND($H2<0,$K2<-0.3,$P2<12)


And I'd be stunned if I didn't make a mistake, so you'll likely have to tweak.

So set those rules for a cell in row 2, then do a format copy to all other cells. If the cell you are setting it to is not in row 2 then adjust the formulas accordingly.

oh sorry it's 2010...and I might have different conditions, but I'll check on Monday.

Thanks W.P. Rager and all

shabbs wrote:Hmmmm... you're looking for conditional formatting using three different values under three different conditions. You should be able to use a formula that evaluates those cells based on your conditions to determine the resulting colour of the cell you are setting this up in. The formulae would be a series of OR evaluations for each of your three tests.

Conditional formatting doesn't work like that. You cannot have a single rule with multiple outcomes. You could do it with VBA, of course, but not conditional formatting.

tim1_2 wrote:Excel has a pretty good built-in tool for conditional formatting, but I don't know how to make the value in one cell affect the formatting in a different cell, if that's what you're going for.

Under Conditional Formatting select New Rule and you get this interface:


Click on the last option -- "Use a formula to determine which cells to format". Any cell references in the formula are relative to the cell where you were when you started (in my examples I assumed a heading row 1, then the first cell to format was in cell 2). When you copy the formatting to the cell below, that 2 will automatically change to a 3. The $ in front of the column marker is to make sure that the formula always checks the cell in that column. This way you can copy this formatting to a range of cells in the same row.

wprager wrote:You cannot have a single rule with multiple outcomes.

Sure you can. I do it all the time with statuses to show progress for tasks (Not Started, In Progress, Completed, Error). I have a single conditional statement that sets the colour depending the status. That is the whole point of conditional.

I'm the king of excel