use conditional formatting



setup 4 rules , 1 for each colour

for 2007 or 2010 excel version
Conditional Formatting

Highlight applicable range >>
=$I$3:$I$30,$E$3:$F$30,$A$3:$B$30

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=$A3=1

Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

now repeat for the other values and colours
use the $ so all columns are tested against cell A

see attached sheet conditional format for all 4 colours and numbers

Attached Files

colours based on numbers and non contigous cells.xlsx (8.8 KB, 24 views)