Excel Dubai wrote:
Hi,

I have a sheet with 3 columns.
A is the list of 2500 references
B is the list of 6500 lot numbers
C is the quantity per lot per reference
I would like to change the format whenever I change the reference. Example

ref 1, Lot A, Qty 6
ref 1, Lot B, Qty 1
ref 1, Lot C, Qty 2
ref 5, Lot D, Qty 9
ref 5, Lot E, Qty 7
ref 2, Lot F, Qty 6,
ref 3, Lot G, Qty 8

I would like all lines of ref 1 to be displayed in blue, then ref 5 with no
colors and then ref 2 in blue and then ref 3 with no colors etc...
Please help.

Kind regards,

Marouane


One way...

Assuming your data starts in A2, select the entire range starting with A2,
select Format / Conditional Formatting, select "Formula Is" and enter the following:

=MOD(SUM(IF(FREQUENCY(MATCH($A$2:$A2,$A$2:$A2,0),M ATCH($A$2:$A2,$A$2:$A2,0))0,1)),2)

Then select the alternating format you want.