|
|
Alternating background row colour |
|
Author |
Message |
MauricioFranca
|
Posted: Mon May 29 08:45:13 CDT 2006 |
Top |
Excel >> Alternating background row colour
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.
Excel339
|
|
|
|
|
Jim
|
Posted: Mon May 29 08:45:13 CDT 2006 |
Top |
Excel >> Alternating background row colour
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.
|
|
|
|
|
Bob
|
Posted: Mon May 29 08:51:05 CDT 2006 |
Top |
Excel >> Alternating background row colour
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)
> 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.
>
>
|
|
|
|
|
Ardus
|
Posted: Mon May 29 09:15:38 CDT 2006 |
Top |
Excel >> Alternating background row colour
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
>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.
>
|
|
|
|
|
Davie
|
Posted: Mon May 29 09:32:01 CDT 2006 |
Top |
Excel >> Alternating background row colour
Thanks very much guys! It's appreciated.
>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)
>
>> 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.
>>
>>
>
>
|
|
|
|
|
|
|