Alternate Cell shading

[jlangella36]

Is there an easy way to shade every other cell based on the data within the cell.

for example.

1 - shade grey
1 - shade grey
1 - shade grey
1 - shade grey
2
2
3 - shade grey
4
4
4
5 - shade grey
5 - shade grey
5 - shade grey
5 - shade grey
6
7 - shade grey
8

this needs to be done for thousands of records and will be sort on the name, it wont be numbers, i just used that as an example, there will be at least 100 unique records, so would be hard to program.

thanks,

Jeff

 

[Ben Dover]

I have not used it much but the free ASAP add in has a few shading tools that are easy to use

Ben

 

[Brian from Maui]

If you want every other row shaded using Conditional Formatting, highlight your range, go to Format/Conditional Formatting and Formula is, condition 1

=MOD(ROW(),2)=0

condition 2

=MOD(ROW(),2)=1

 

[pcc]

try this

Sub yui()
r = 2
1:
Do While Not IsEmpty(Cells(r, 1))
Do While Cells(r, 1) = Cells(r + 1, 1)
Cells(r, 1).Interior.ColorIndex = 15
r = r + 1
Loop
Cells(r, 1).Interior.ColorIndex = 15
r = r + 1

Do While Cells(r, 1) = Cells(r + 1, 1)
Cells(r, 1).Interior.ColorIndex = xlNone
r = r + 1
Loop
Cells(r, 1).Interior.ColorIndex = xlNone
r = r + 1
GoTo 1
Loop

End Sub

 

[jlangella36]

thanks pcc,

that worked great, it came up with a runtime error, but overall it worked.

thanks again,

Jeff

 

[Peter_SSs]

jlangella36

Welcome to the Mr Excel board!

For a Conditional Formatting solution as shown below.

1. Select A2:A??
2. Format|Conditional Formatting...|Formula Is:
=MOD(SUMPRODUCT(--(A$1:A1<>A$2:A2)),2)
3. Click Format... and choose colour
4. OK, OK

Mr Excel 05 11 20.xls
	A	B	C	D
1	Fred
2	Fred
3	Jan
4	Jan
5	Jan
6	Sam
7	Sam
8	Ann
9	Bill
10	Bill
11	Bill
12	Sue
13	Sue
14
Conditional Format