Conditionnal Formatting

E

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
 
G

Glenn

Excel said:
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.
 
G

Glenn

Glenn said:
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)
 
G

Glenn

Glenn said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top