+ Reply to Thread
Results 1 to 10 of 10

Row color by grouping?

  1. #1
    guy
    Guest

    Row color by grouping?

    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?

  2. #2
    Ken Wright
    Guest

    Re: Row color by grouping?

    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?




  3. #3
    guy
    Guest

    Re: Row color by grouping?

    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.

  4. #4
    Jim Thomlinson
    Guest

    RE: Row color by grouping?

    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?
    >


  5. #5
    Ken Wright
    Guest

    Re: Row color by grouping?

    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.




  6. #6
    Jim Thomlinson
    Guest

    Re: Row color by grouping?

    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?

    >
    >
    >


  7. #7
    Ken Wright
    Guest

    Re: Row color by grouping?

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

    > >
    > >
    > >




  8. #8
    guy
    Guest

    Re: Row color by grouping?

    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.

  9. #9
    Myrna Larson
    Guest

    Re: Row color by grouping?

    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?

    >>
    >>
    >>



  10. #10
    guy
    Guest

    Re: Row color by grouping?

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1