performance mgt report color coding

[Colanthop]

Can you help? I have been asked to create a color coded performance management report in excel which shows a target value eg 100 and actual value eg 95, a variance eg -5 and a percentage variance eg -5%. I have to award a background color of Green, Amber or Red to the variance. The conditions are that if a variance cell >= 0 then the background is green; if the variance cell is equal to or greater than -5% of the target (and less than 0) then the background is amber, it is also amber if the negative variance is small ie between 0 and -50. Outside of these conditions the variance is red. I have tried the conditional formating in excel which only allows for 3 conditions. Is there a way round this using visual basics. The background color would be applied to a range of cells. Can anyone help with the code.

many thanks

[ResulG]

Hi,

I dont know how you could do it in VBA but an idea i an give you is to split the data into two columns.
This will double the Conditional Formatting option and you will be able to format the data in 6 conditions.

You can then put the data back into one column.

I dont know if this is appropriate to be applied to the report but hope it works.

ResulG