CODEWITHSUNDEEP
excelrowhighlight
frank
frank

Reputation: 3598

highlight rows for different values in excel

I have

enter image description here

I want to highlight each row that has a different value to the one above it in a different colour. Makes it easier to view all the category 3, for example, especially when I have lots of columns, and it is easier than filtering on each value to view

enter image description here

Upvotes: 0

Views: 27

Answers (1)

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2609

You can use an extra column to determine what rows to highlight:

id category highlight
1 1 =IF(B2=B1,C1,IF(ISTEXT(C1),1,C1+1))
2 2 =IF(B3=B2,C2,IF(ISTEXT(C2),1,C2+1))
3 3 =IF(B4=B3,C3,IF(ISTEXT(C3),1,C3+1))
4 3 =IF(B5=B4,C4,IF(ISTEXT(C4),1,C4+1))
5 4 =IF(B6=B5,C5,IF(ISTEXT(C5),1,C5+1))
6 4 =IF(B7=B6,C6,IF(ISTEXT(C6),1,C6+1))
7 4 =IF(B8=B7,C7,IF(ISTEXT(C7),1,C7+1))
8 6 =IF(B9=B8,C8,IF(ISTEXT(C8),1,C8+1))
9 6 =IF(B10=B9,C9,IF(ISTEXT(C9),1,C9+1))
10 6 =IF(B11=B10,C10,IF(ISTEXT(C10),1,C10+1))
11 3 =IF(B12=B11,C11,IF(ISTEXT(C11),1,C11+1))
12 3 =IF(B13=B12,C12,IF(ISTEXT(C12),1,C12+1))
13 3 =IF(B14=B13,C13,IF(ISTEXT(C13),1,C13+1))

Then you can use conditional formatting with this formula:

=ISEVEN($C1)

Upvotes: 1

Related Questions