Fill Color each Row based on a Condition
 

I have a spreadsheet with customer's account numbers in column A and other
data associated with the account number through column Z.

I'm looking for a way to use the fill color (gray) for the entire row if the
account numbers match, then for the next match use fill color (white) and so
on.

A
1 12345 gray
2 12345 gray
3 12345 gray
4 67891 white
5 67891 white
6 57842 gray
7 54123 white
8 54123 white

Thanks in advance for the help.

Fill Color each Row based on a Condition
 

Donnie,

Try the following code

Sub ColorGray()
Dim Rng As Range
Dim OldVal As Variant
Dim Gray As Boolean

Gray = True
OldVal = Range("A1").Value
For Each Rng In Range("A1:A10") ' CHANGE TO CORRECT RANGE
If Rng.Value = OldVal Then
If Gray Then
Rng.EntireRow.Interior.ColorIndex = 15
Else
Rng.EntireRow.Interior.ColorIndex = 2
End If
Else
OldVal = Rng.Value
Gray = Not Gray
If Gray Then
Rng.EntireRow.Interior.ColorIndex = 15
Else
Rng.EntireRow.Interior.ColorIndex = 2
End If
End If
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Donnie Stone" wrote in message
...
I have a spreadsheet with customer's account numbers in column A and other
data associated with the account number through column Z.

I'm looking for a way to use the fill color (gray) for the entire row if
the
account numbers match, then for the next match use fill color (white) and
so
on.

A
1 12345 gray
2 12345 gray
3 12345 gray
4 67891 white
5 67891 white
6 57842 gray
7 54123 white
8 54123 white

Thanks in advance for the help.

Fill Color each Row based on a Condition
 

Chip,

Works great, I appreciate the help!

Donnie

"Chip Pearson" wrote in message
...
Donnie,

Try the following code

Sub ColorGray()
Dim Rng As Range
Dim OldVal As Variant
Dim Gray As Boolean

Gray = True
OldVal = Range("A1").Value
For Each Rng In Range("A1:A10") ' CHANGE TO CORRECT RANGE
If Rng.Value = OldVal Then
If Gray Then
Rng.EntireRow.Interior.ColorIndex = 15
Else
Rng.EntireRow.Interior.ColorIndex = 2
End If
Else
OldVal = Rng.Value
Gray = Not Gray
If Gray Then
Rng.EntireRow.Interior.ColorIndex = 15
Else
Rng.EntireRow.Interior.ColorIndex = 2
End If
End If
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Donnie Stone" wrote in message
...
I have a spreadsheet with customer's account numbers in column A and
other
data associated with the account number through column Z.

I'm looking for a way to use the fill color (gray) for the entire row if
the
account numbers match, then for the next match use fill color (white)
and
so
on.

A
1 12345 gray
2 12345 gray
3 12345 gray
4 67891 white
5 67891 white
6 57842 gray
7 54123 white
8 54123 white

Thanks in advance for the help.