Conditional formatting

[Debbie Uniforms]

Hello,

I would like to format cells based on the value in another cell.

Eg, column A has names of staff. column B gives the score from an assessment.

I would like the name (column A) to change to red if the score in column B is 1. I'd like the name to change to orange if the score in column B is 2, etc etc.

I just know it can be done AND, I just know that one of you clever people can help me!

many thanks
Debbie Uniforms.

 

[sandy666]

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

1​

[/td][td=bgcolor:#00B0F0]Adam[/td][td]

1​

[/td][td][/td][td]blue[/td][td] =IF(G1=1,1,0)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

2​

[/td][td=bgcolor:#92D050]Mike[/td][td]

2​

[/td][td][/td][td]green[/td][td] =IF(G1=2,1,0)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

3​

[/td][td]John[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

4​

[/td][td=bgcolor:#00B0F0]Peter[/td][td]

1​

[/td][td][/td][td]select range then use above[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

5​

[/td][td]Mike[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

6​

[/td][td]Sue[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

7​

[/td][td=bgcolor:#92D050]Steve[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

8​

[/td][td=bgcolor:#00B0F0]Alan[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

or

=AND(G1=1,1)

=AND(G1=2,1)

 

[sandy666]

or even

=G1=1

=G1=2

 

[Debbie Uniforms]

Hi its me again

I don't really follow!

[TABLE="width: 218"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]JobTitle[/TD]
[TD]Average score[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]agent[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]susan[/TD]
[TD]supervisor[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]manager[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Abdulla[/TD]
[TD]agent[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Raj[/TD]
[TD]agent[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

I would like the names of those who score 6 to be green, 5 light green, 4 orange, 3 yellow, 2 light red and 1 red.

I think this is in conditional formatting but not sure which rules to add etc.

I have been able to do it, when I add a number next to staff name - eg: Bob 1, but I want the average score in a separate column.

many thanks and sorry for being thick!

 

[sandy666]

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

1​

[/td][td]Name[/td][td]JobTitle[/td][td]Average score[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

2​

[/td][td]Bob[/td][td]agent[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

3​

[/td][td]susan[/td][td]supervisor[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

4​

[/td][td]Fred[/td][td]manager[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

5​

[/td][td]Abdulla[/td][td]agent[/td][td]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

6​

[/td][td]Raj[/td][td]agent[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

7​

[/td][td=bgcolor:#FF0000]Jon[/td][td]agent[/td][td]

1​

[/td][/tr]
[/table]

and do the same for each color , C1=2, C1=3 and so on...

edit:
each time add new rule for different color
rule what you should add is grey highlighted

I have been able to do it, when I add a number next to staff name - eg: Bob 1, but I want the average score in a separate column.

select whole range
then do the same for column where your numbers are

if your numbers are in Z column then select range A1:Zn and use rule =Z1=1 and so on

 

[AlanY]

basically just apply the CF value equal to ... six times, see attached

https://drive.google.com/file/d/1RquiE7F0bfYT8KNRiI3f26YnLm95PUMm/view?usp=sharing

 

[Debbie Uniforms]

basically just apply the CF value equal to ... six times, see attached

Hi Alan, I couldn't see an attachment

Thanks

 

[AlanY]

Hi Alan, I couldn't see an attachment

Thanks

try again

 

[sandy666]

or select only column where you want colors and add the same rules

 

[Debbie Uniforms]

or select only column where you want colors and add the same rules

Typical, I can't select the link at work as blocked! I'll have to look at home.