Row color by grouping?

[guy]

I have some product codes in 3 columns that are sorted. I would like to alternate the color of rows
when a product code changes. How done?

 

[Ken Wright]

Additional column, assume A.
Assume your data is now in Col B and is sorted on Col B such that changes in
Col B denote required change of colour.
In A2 =--(B2<>B1)
In A3 =(B3<>B2)+A2 and copy down as far as your data goes

Select the entire sheet, do Format / Conditional Formatting, change cell
value is to Formula is and put in

=MOD($A1,2)=0

Choose a colour from the pattern tab on the format dialog box and hit OK

 

[guy]

Additional column, assume A.
Assume your data is now in Col B and is sorted on Col B such that changes in
Col B denote required change of colour.
In A2 =--(B2<>B1)
In A3 =(B3<>B2)+A2 and copy down as far as your data goes

Select the entire sheet, do Format / Conditional Formatting, change cell
value is to Formula is and put in

=MOD($A1,2)=0

Choose a colour from the pattern tab on the format dialog box and hit OK

What if my data is in Col B, Col C and Col D? Changes in either of these denote required change of
colour.

Thank you for your time.

 

[Guest]

Here it is in code if you are using conditional formats already for a
different purpose. You will probably want to cahnge the colours which just
requires changing intCoulour1 and intColour2. These should really be
constants anyway...

Sub test()
Dim rngToColour As Range
Dim varLastValue As Variant
Dim intColour1 As Integer
Dim intColour2 As Integer
Dim intCurrentColour As Integer

intColour1 = 2
intColour2 = 3

intCurrentColour = intColour1

Set rngToColour = Sheet1.Range("A2:C2")
varLastValue = rngToColour.Value(1, 1)
Do While rngToColour.Value(1, 1) <> ""
rngToColour.Interior.ColorIndex = intCurrentColour
If rngToColour.Value(1, 1) <> varLastValue Then
If intCurrentColour = intColour1 Then
intCurrentColour = intColour2
Else
intCurrentColour = intColour1
End If
varLastValue = rngToColour.Value(1, 1)
End If
Set rngToColour = rngToColour.Offset(1, 0)
Loop
End Sub

HTH

 

[Ken Wright]

A2 =--(B2&C2&D2<>B1&C1&D1)
A3 =(B3&C3&D3<>B2&C2&D2)+A2 and copy down

Hide Col A and you won't even know it's there

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Guest]

That is a unique solution that I just had not thought of... Very interesting.
I knew that I hung out in this forum for a reason...

 

[Ken Wright]

My last post covers the three columns instead of just one

 

[guy]

Here it is in code if you are using conditional formats already for a
different purpose. You will probably want to cahnge the colours which just
requires changing intCoulour1 and intColour2. These should really be
constants anyway...

Sub test()
Dim rngToColour As Range
Dim varLastValue As Variant
Dim intColour1 As Integer
Dim intColour2 As Integer
Dim intCurrentColour As Integer

intColour1 = 2
intColour2 = 3

intCurrentColour = intColour1

Set rngToColour = Sheet1.Range("A2:C2")
varLastValue = rngToColour.Value(1, 1)
Do While rngToColour.Value(1, 1) <> ""
rngToColour.Interior.ColorIndex = intCurrentColour
If rngToColour.Value(1, 1) <> varLastValue Then
If intCurrentColour = intColour1 Then
intCurrentColour = intColour2
Else
intCurrentColour = intColour1
End If
varLastValue = rngToColour.Value(1, 1)
End If
Set rngToColour = rngToColour.Offset(1, 0)
Loop
End Sub

HTH

Good stuff. If I am reading this correctly then I would probably set colorindex after determining
intCurrentColour.

 

[Myrna Larson]

Here's a variation on the same theme. In A1 put the number 1. In A2
=IF(B2<>B1,1-A1,A1). For the CF condition, use 0 or 1 and set the format for
those rows.

 

[guy]

A2 =--(B2&C2&D2<>B1&C1&D1)
A3 =(B3&C3&D3<>B2&C2&D2)+A2 and copy down

Hide Col A and you won't even know it's there

Good stuff.