Change background colour of a cell based on value of another cell

**maryfay** · 09-07-2012, 10:14 AM

I have the following data:

A B
1 Brian 36
2 Tony 7
3 Lisa 8
4 Abe 0
5 Phil 25
6 May 0

Is it possible to colour the name in Column based on the value in Column B.
For example if a staff member has a result of 0 then the cell with their name will
have a backgroud colour of Red. If they get >0 but <19 then they will
have a background colour of blue. For a result of >=19 but less than 100
the background colour would be green.

Abe and May would get a background colour of Red as they have both a 0 result
Tony and Lisa would get a background colouor of Blue, they are >0 but less than 19
Brian and Phil would get a background colour of Green, they have >19 but less than 100

Any help would be much appreciated, then I can could the cells based on their colour.
Mary

**Special-K** · 09-07-2012, 10:19 AM

Use Conditional Formatting

Select A1
New Rule, use a formula to determine...
=(B1=0)
Format as red

New Rule, use a formula to determine...
=(AND(B1>0,B1<19)
Format as Blue

New Rule, use a formula to determine...
=(AND(B1>=19,B1<100)
Format as Green

Use Format Painter to copy down column A

**Ace_XL** · 09-07-2012, 10:25 AM

Use conditional formatting..

See attached.

Any help would be much appreciated, then I can could the cells based on their colour.

For counting cells you could simply use COUNTIF/COUNTIFS

hence,
=countif(Range,0)
=COUNTIFS(Range,"<19",Range,"<>0")
=COUNTIFS(Range,"<100",Range,">=19")