Colouring boxes depending on values

**se7encm** · 08-20-2010, 03:02 AM

Any help would be gratefully recieved

Premise:
I'm trying to colour code student results, essentialy traffic light students on how close to their target grade they were/are:

0 = Yellow
1 or more = Green
-1 or lower = Amber

Now I know how to do it for the cell that the values are in via conditional formating, so in mine the value is in I4 and this is correct colour.

However I would also like their name in the corresponding colour so cell A4.

Ideally is their one formula that would use the cell value in I4 to colour the cells of I4 and A4 in one of 3 colours depending on the value?

Many thanks for any help you can provide - and please keep it simple as I'm a real dummy when it comes to excel

Cheers

**teylyn** · 08-20-2010, 03:31 AM

If you have the conditional formatting sorted for column I, then that's fine. Good on you.

Now, select all cells in column A, for example from A2 to A100 with A1 having labels
Click Format - Conditional Formatting
Click Formula Is in the first drop down box
in the formula box enter =$I2=0
click the format button and select the format yellow and close the format box with OK
click Add
click Formula Is
enter the formula =$I2>=1
click the format button and select the pattern format green and close the format box with OK
click Add
click formula Is
enter the formula =$I2<=-1
click the format button and select the pattern format Amber and close the format box with OK

click OK

**TMS** · 08-20-2010, 03:34 AM

In cell A2 and I2 use Conditional Formatting | yellow. Formula is "=$I2=0" (without quotes) and format with background colour yellow. Similarly for other colours ("=$I2>=1"and green) and pairs of cells.

Regards

**se7encm** · 08-20-2010, 06:42 AM

Thanks guys

**teylyn** · 08-20-2010, 07:03 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save