Conditionnal Formatting

[Excel Dubai]

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

 

[Glenn]

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),MATCH($A$2:$A2,$A$2:$A2,0))>0,1)),2)

Then select the alternating format you want.

 

[Glenn]

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),MATCH($A$2:$A2,$A$2:$A2,0))>0,1)),2)


Then select the alternating format you want.

Actually, that will fail if your "ref #" repeats. Try this instead:

=MOD(SUMPRODUCT(($A$2:A2<>$A$1:A1)*1),2)

 

[Glenn]

Actually, that will fail if your "ref #" repeats. Try this instead:

=MOD(SUMPRODUCT(($A$2:A2<>$A$1:A1)*1),2)

Correction...

=MOD(SUMPRODUCT(($A$2:$A2<>$A$1:$A1)*1),2)