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).
FormatConditional Formatting Formula Is:

=$A3<$A2

Set the condition to yellow.

HTH
Kostis Vezerides

On Jul 25, 11:44 am, Sarah (OGI)
wrote:
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?

I don't think this does what is required: it doesn't colour all cells for a
given client.

"vezerid" wrote:

Select all the cells that you want colored, except the first row of
data (i.e. from row 3 and down).
FormatConditional Formatting Formula Is:

=$A3<$A2

Set the condition to yellow.

HTH
Kostis Vezerides

On Jul 25, 11:44 am, Sarah (OGI)
wrote:
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?

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!

"Toppers" wrote:

I don't think this does what is required: it doesn't colour all cells for a
given client.

"vezerid" wrote:

Select all the cells that you want colored, except the first row of
data (i.e. from row 3 and down).
FormatConditional Formatting Formula Is:

=$A3<$A2

Set the condition to yellow.

HTH
Kostis Vezerides

On Jul 25, 11:44 am, Sarah (OGI)
wrote:
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?

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

"Toppers" wrote:

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!

"Toppers" wrote:

I don't think this does what is required: it doesn't colour all cells for a
given client.

"vezerid" wrote:

Select all the cells that you want colored, except the first row of
data (i.e. from row 3 and down).
FormatConditional Formatting Formula Is:

=$A3<$A2

Set the condition to yellow.

HTH
Kostis Vezerides

On Jul 25, 11:44 am, Sarah (OGI)
wrote:
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?