Conditional formatting??

[vvvalerie]

This is probably really simple, but I am looking for the easiest way to do this in Excel 2007..

I have to turn a cell yellow at non-sequential numbers ie: normal at 1-3, yellow at 4, green at 5, back to normal, yellow at 9, green at 10...

How would I go about doing this?

Thanks!

 

[Jonmo1]

For Yellow (4,9,14,19,etc)
=MOD(A2,5)=4

For Green (5,10,15,20, etc)
=MOD(A2,5)=0

All else (1,2,3,6,7,8,11,12,13,etc) will remain unformatted.


Hope that helps.

 

[vvvalerie]

That does help - I got it to do it for the 4 values with that formula, but for some reason, it wouldn't work with the other values? I uploaded a copy of the workbook with the data removed from it - row 5 is the one that will need to be changing to green or yellow. It is the entire row, but if I can get the formula working for 1 cell, I can do it for all. The value in the cells in row 5 can go up to an infinite number.

http://www.zshare.net/download/89771732d64e1694/

Sorry - I am EXTREMELY rusty with Excel.. haven't had to do much with it in quite a while.

 

[Jonmo1]

Highlight the WHOLE row from B5 to AA5
go to conditional formatting - new rule - use a formula

Then enter the formula as given above and apply the format
You'll have to make 2 rules, one for yellow ( mod(..)=4 ) one for green ( mod(..)=0 )

 

[vvvalerie]

Still not working - I get the entire row changing to yellow or green instead of the cells changing color depending on what their value is.

Edit - nevermind, I fixed it. I had to take out the $ signs... didn't think it would matter.

Thanks for your help!

 

[Jonmo1]

make sure you're not using absolute references...

Use
=MOD(A2,5)=4

NOT
=MOD($A$2,5)=4
or
=MOD($A2,5)=4