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.

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


"Davie" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
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.

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)

"Davie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>

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
--
AP

"Davie" <(E-Mail Removed)> a écrit dans le message de news:
(E-Mail Removed)...
>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.
>

Thanks very much guys! It's appreciated.


"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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)
>
> "Davie" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>
>>
>
>