Alternating background row colour  
Author Message
MauricioFranca





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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.
>>
>>
>
>