Alternating background row colour

D

Davie

I have a spreadsheet with a list of outlet names and various associated
information. I want to have an alternating background colour when the name
changes. For example

Name Details
abc this is the details
abc more details
abc even more details
xyz xyz details
xyz more details
test test


so for the above example, the background colour of abc would be the same,
then it would alternate when the name changes to xyz, and then change again
for test. The spreadsheet i have has several tens of thousands of entries
and cannot be done manually. Any suggestions?

Thanks in advance.
 
J

Jim Cone

There is the free Excel add-in "Shade Data Rows" at my website that
shades rows by value or by every nth row. You can use any color
in the Excel color palette. It should do what you want. Download from...
http://www.realezsites.com/bus/primitivesoftware

Jim Cone
San Francisco, USA


I have a spreadsheet with a list of outlet names and various associated
information. I want to have an alternating background colour when the name
changes. For example

Name Details
abc this is the details
abc more details
abc even more details
xyz xyz details
xyz more details
test test


so for the above example, the background colour of abc would be the same,
then it would alternate when the name changes to xyz, and then change again
for test. The spreadsheet i have has several tens of thousands of entries
and cannot be done manually. Any suggestions?

Thanks in advance.
 
B

Bob Phillips

A macro to do it

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim ci As Long
Dim vVal

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
vVal = Range("A2").Value
ci = 35
For i = 2 To iLastRow
If Cells(i, "A").Value <> vVal Then
If ci = 35 Then
ci = xlColorIndexNone
Else
ci = 35
End If
vVal = Cells(i, "A").Value
End If
Rows(i).Interior.ColorIndex = ci
Next i

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)
 
A

Ardus Petus

With a staging column (say in column C)
Enter in C2
=IF(A2=A1,C1+1,C1)
and drag down

Select all cells
Format>Conditional Format
Formula: =MOD(C1,2)=1
Select format, background shading you like

Et voilà!

HTH
 
D

Davie

Thanks very much guys! It's appreciated.


Bob Phillips said:
A macro to do it

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim ci As Long
Dim vVal

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
vVal = Range("A2").Value
ci = 35
For i = 2 To iLastRow
If Cells(i, "A").Value <> vVal Then
If ci = 35 Then
ci = xlColorIndexNone
Else
ci = 35
End If
vVal = Cells(i, "A").Value
End If
Rows(i).Interior.ColorIndex = ci
Next i

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)
Click to expand...
 
Top