Formatting cells in one sheet based on their values in another sheet

Hello everyone =)

I have 2 sheets in one workbook - one contains planned values and the other actual values. I'm trying to format the second sheet to show differences, for example: cell C6 in Sheet1 is 10 so if C6 in Sheet2 is higher I'd like it to be highlighted green and if it's lower highlighted in red. I know it's easily done for one cell but I need it for the whole sheet (would be a loooot of clicking to do it one by one). I've found this thread: http://www.excelforum.com/excel-prog...orksheets.html - but it only highlights differences in yellow. Could it be changed to two color scale for lower and higher values?

Thanks and regards,

Bart

Hi,

You could use conditional formating.


Simeon

Yes, i figured that much but how? If you format cells from the ribbon using formula for each one you'll have to go cell by cell which is very time consuming so I was thinking VBA can do it quickly for all the cells in a sheet... If you check the link provided you'll see what I mean - I'd like the same thing to happen on my sheet but with two color scale.

Thanks,

Bart

Bart,

Ah, I see now. Sorry about that. I have come up with a way to identify different values without using VBA because I am not that skilled with it yet.

In the attached Workbook I just added another column that uses a simple IF statement to identify if "Planned Values" are >,<, or = to the values in "Actual Values" and return either a 1, -1, or 0.

Then using the built in Conditional Formating I highlighted the entire column and applied the specified criteria i.e. (IF Actual Values!B2 is greater than Planned Values! B2 then "Green")

Hope this helps.


Simeon