format cell colour dependant on comparison to target value and 0

Hi,

I have a spreadsheet with columns for Client, Target DC%, The actual figure attainted last 3 months and the DC% year to date which looks like this:

Client TargetDC% DC% last 3 months DC% YTD
1 4% -1% -51%
2 31% 57% 57%
3 45% 51% 28%


I need to create a macro that will highlight the cells in Columns C and D based on the criteria below but to highlight the background of the cell that the number is in not update a new cell. This same criteria should be applied to both column C and column D values versus those in column B.

=IF(AND(C2 >B2,C2>0),"Green",IF(AND(C2 <B2,C2>0),"Amber",IF(AND(C2 <B2,C2<0),"Red","none")))


I made some progress in that I can get it to check one of the items, e.g. highlight if cellC2>B2 but not the additional greater than zero check or all the others!

I do hope I am not repeating another post but I have tried and tried to find the answer!

Any help would be greatly received as its sending me crazy. I know excel pretty well but am no programmer so the macro element has me dizzy.

Thanks in advance

Kirsten

Hey Kristen,

Try this code:

Please Login or Register  to view this content.

To put it in your workbook, press Alt+F11 and paste the above code in there.

Why do you need a Macro for this ? Surely conditional formatting would suffice ?

Using your sample file, highlight C2:D14 and apply the following Conditional Formatting "FormulaIs" Rules:

Rule 1
=AND(C2>$B2,C2>0)
format Green

Rule 2
=AND(C2<$B2,C2>0)
format Amber

Rule3
=AND(C2<$B2,C2<0)
format Red

as the values alter so too will the formatting.


EDIT:
@shampoo monkey, FWIW, in the below:

Please Login or Register  to view this content.

only BVal is a Double - all others are declared as Variants - not sure if that was the intention or not - it always makes sense (IMO) to declare variables which will store whole numbers > 255 (or < 0) as Longs
(Integers get converted to Long under the hood)