Code to change cell colour

[Damo10]

Hi,

I would like some code that will change the cell colour for all the cells in columns U:AK as long as the machine number in column U is the same.

The data is sorted by machine number in column U but the number of rows for each machine number will change daily sometimes it may be 1 row or as many as 20, what i would lke is to have 2 colours so it is easy to tell apart the different machines

See example below

Excel Workbook
	U
4	5
5	5
6	5
7	5
8	5
9	23
10	23
11	23
12	23
13	23
14	33
15	33
16	33
17	33
Data

Excel 2010

 

[Jerry Sullivan]

Hi,

You can do that with two Conditional Formatting Rules...

Excel Workbook
	U
3
4	5
5	5
6	5
7	5
8	5
9	23
10	23
11	23
12	23
13	23
14	33
15	33
16	33
17
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
U4	1. / Formula is =MOD(SUM(IF(U$4:U4<>U$3:U3,1,0)),2)=0	Abc
U4	2. / Formula is =MOD(SUM(IF(U$4:U4<>U$3:U3,1,0)),2)<>0	Abc

 

[Peter_SSs]

Given that Conditional Formatting is volatile you could cut down the amount of processing overhead as follows ..

1. Colour all the rows manually one of the colours (I used the blue colour)
2. Use a helper column. Enter the formula shown and copy down. (You could then hide this column)
3. Select U4:AK? and apply the CF shown.

Excel Workbook
	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
3
4	5																	1
5	5																	1
6	5																	1
7	5																	1
8	5																	1
9	23																	0
10	23																	0
11	23																	0
12	23																	0
13	23																	0
14	33																	1
15	33																	1
16	33																	1
17	33																	1
CF Groups

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
U4	1. / Formula is =$AL4	Abc