Conditional Formatting for alternate client refs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have multiple rows, each with a client ref in column A (in the format:
B01234) - the data is sorted in ascending order by column A.
I would like to define some conditional formatting, so that alternate client
refs are either yellow or white - where a client ref is repeated, the rows
are to be one colour. For example, if rows 2-5 relate to client ref B01234,
I want all the rows to be yellow. If rows 6-7 relate to another client, I
want the rows to be white. Then if row 8 was yet another client, the row
should be yellow again.
Any ideas what I should enter into the conditional formatting prompt?
 
Select all the cells that you want colored, except the first row of
data (i.e. from row 3 and down).
Format>Conditional Formatting> Formula Is:

=$A3<>$A2

Set the condition to yellow.

HTH
Kostis Vezerides
 
I had to use a helper column (in my case column C) with :

=INT(SUM(1/COUNTIF($A$2:A2,$A$2:A2)))

Enter with Ctrl+Shift+Enter and copy down column A

then set CF:

FormulaIs: =MOD($C2,2) and set to yellow

There may (will!) be a smarter way

Limited testing!
 
Hey, that worked a treat, thank you so much!

Toppers said:
I had to use a helper column (in my case column C) with :

=INT(SUM(1/COUNTIF($A$2:A2,$A$2:A2)))

Enter with Ctrl+Shift+Enter and copy down column A

then set CF:

FormulaIs: =MOD($C2,2) and set to yellow

There may (will!) be a smarter way

Limited testing!
Click to expand...
 
Back
Top