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?
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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.
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?
>
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" <[email protected]> wrote in message
news:[email protected]...
> On Fri, 4 Feb 2005 00:49:58 -0000, "Ken Wright"
<[email protected]> 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.
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> 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.
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"
<[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 01:14:14 -0000, "Ken Wright" <[email protected]> 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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks