Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    21

    Unanswered: Color banding help

    Hello everyone,

    I'm not proficient enough in Excel VBA so I figure I would turn to the gurus. Anyhow, I have a worksheet with many records and I want to alternate between two colors (white and gray) based on the contents of a cell. For example, I have a list of last names and I want the names that are the same and next to one another to be the same color. I would like the colors to alternate as well. Example:

    ROSS (white)
    ROSS (white)
    ADAMS (gray)
    WEBSTER (white)
    MOSS (gray)
    MOSS (gray)
    WOLF (white)
    ...

    Any help would be greatly appreciated. Thanks in advance.

    Bobbio

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ive thrown this quick example together for you

    Code:
    Sub Test()
        Dim MyName As String
        Dim ColorOn As Boolean
        Dim Lastrow As Integer, i As Integer
        
        'Initalise variables
        ColorOn = True
        Lastrow = Range("A" & Rows.Count).End(xlUp).Row
        
        'Start Loop
        For i = 2 To Lastrow
            MyName = Range("A" & i - 1).Formula
            'check for duplicate entries
            If Range("A" & i).Formula = MyName Then
                'choose same color as cell above
                Range("A" & i).Range("A1:C1").Interior.ColorIndex = Range("A" & i - 1).Interior.ColorIndex
            Else
                'switch color
                ColorOn = Not ColorOn
                If ColorOn = True Then
                    'First Three coloumns coloured
                    Range("A" & i).Range("A1:C1").Interior.ColorIndex = 15
                End If
            End If
         Next i
                
    End Sub
    If you would like an explanation of how this works or is you have any other queries please just post back.

    HTH

    David

Posting Permissions

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