Automating conditional formatting based on > or < cell reference

Hey everyone,

I'm working on conditionally formatting 200+ cells to show up as either red or green based on if the numbers in them are greater or less than they were in the previous report. Its very tedious to have to create two separate rules to do this. Can anyone think of a way to do a macro or something to automate it?

The two charts are exactly the same size so the two cells that are being compared are always the same distance from each other if that helps. Looks like this basically:

If $K$1>$A$1 then color green
If $K$1<$A$1 then color red

next

If $K$2>$A$2 then color green
If $K$2<$A$2 then color red

Their are like 10 tables that are in 5x5 matrices

thanks in advance!

You can apply the conditional formatting to all the cells in one operation, by highlighting the cells first and then using relative cell references instead of absolute. For example, suppose you want this to apply to the cells in the block A1:E5, then select all those cells with A1 as the active cell, and then you can use these formulae in the CF conditions:

=K1>A1 --> green
=K1<A1 --> red

When you exit the CF dialogue box Excel will automatically adjust the cell references to suit.

If you set it up this way you can also use the Format painter to effectively copy the format (and conditional format) to other cells, eg A11:E15.

Hope this helps.

Pete

Thanks! Removing the $ signs from the cell ranges when making the new conditions did the trick!

Hi I have a spreadsheet where I have used conditional formatting which works fine, however I am wanting the rule to run on a macro so have recorded a macro to highlight sales greater than 200 and sales lower than 2000 which turns the row red and green, the conditonal formatting worked fine but this did not run on the macro. I also recorded a further macro to remove all conditional formatting on the spreadsheet and then expected to click the button for high sales and this to work but nada.

Any suggestions folks.

You should start your own thread rather than tag on to someone else's - take a look at the Forum Rules at the top of the page.

Pete

ok will do thanks.