Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Conditionnal Formatting

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Conditionnal Formatting

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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Conditionnal Formatting

Glenn wrote:
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.




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

=MOD(SUMPRODUCT(($A$2:A2<$A$1:A1)*1),2)
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Conditionnal Formatting

Glenn wrote:
Glenn wrote:
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.




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)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect Cell Formatting including Conditional Formatting Mick Jennings Excel Discussion (Misc queries) 5 November 13th 07 06:32 PM
How to create icon sets for excel 2007 conditionnal formating ? Julien Montserrat Excel Discussion (Misc queries) 1 January 30th 07 06:50 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 3 January 20th 07 03:02 PM
expanding custom formatting without removing existing cell formatting? Keith Excel Worksheet Functions 3 December 27th 06 02:54 PM
How to program a macro to hide rows with conditionnal formating Turquoise_dax Excel Discussion (Misc queries) 5 June 20th 06 04:33 PM


All times are GMT +1. The time now is 01:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"