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?

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

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

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

"guy" wrote in message
...
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?

On Fri, 4 Feb 2005 00:49:58 -0000, "Ken Wright" wrote:

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.

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

"guy" wrote in message
...
On Fri, 4 Feb 2005 00:49:58 -0000, "Ken Wright"
wrote:

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.

On Fri, 4 Feb 2005 01:14:14 -0000, "Ken Wright" wrote:

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.

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" wrote:

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

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

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

"guy" wrote in message
...
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?

:-) My last post covers the three columns instead of just one

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

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

"Jim Thomlinson" wrote in message
...
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" wrote:

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

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


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

--------------------------------------------------------------------------
--

"guy" wrote in message
...
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?

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.



On Thu, 3 Feb 2005 17:15:01 -0800, "Jim Thomlinson"
wrote:

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" wrote:

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

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


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

----------------------------------------------------------------------------

"guy" wrote in message
...
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?

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

"guy" wrote:

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?

On Thu, 3 Feb 2005 17:11:03 -0800, "Jim Thomlinson" wrote:

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.