Forum Discussion
color a column based on values of another column
I have the following table in MS office 2016 (excel) to record the work violation
| No. | person ID | violation | severity |
| 1 | 1234 | violation 1 | high |
| 2 | 1234 | violation 2 | high |
| 3 | 1234 | violation 3 | low |
I need to color the person ID column based on the severity column.
for example, if a person has 2 or more violations with high severity, the person ID column is colored red.
How can I do that?
Thanks 
For this scenario you can apply this rule for conditional formatting:
=AND(COUNTIFS($B$2:$B$6,B2,$D$2:$D$6,"high")>1,D2="high")
4 Replies
=COUNTIFS($B$2:$B$6,B2,$D$2:$D$6,"high")>1
You can apply this rule for conditional formatting
=$B$2:$B$6
and this range the format applies to.
Thanks for replying.
Not that exactly what I meant.
I need to highlight 1234 only if its severity is high only.
But in the photo attached, 1234 is highlighted even though its severity is low
Thanks
For this scenario you can apply this rule for conditional formatting:
=AND(COUNTIFS($B$2:$B$6,B2,$D$2:$D$6,"high")>1,D2="high")
