Results 1 to 3 of 3

Highlight table rows different color for every change in word

This is a discussion on Highlight table rows different color for every change in word within the Excel Questions forums, part of the Question Forums category; Hello, I am using Excel 2007. I have a big spreadsheet that has been sorted by column A, which is ...

  1. #1
    Board Regular
    Join Date
    Feb 2008
    Posts
    60

    Default Highlight table rows different color for every change in word

    Hello,

    I am using Excel 2007.

    I have a big spreadsheet that has been sorted by column A, which is a list of product types--the words can vary, so there are no set words I can give you.

    I don't know how to write code, but I would like a VBA code that will highlight a table row (not an entire row) a certain pastel color for all products that have the same word in column A. When the word changes in column A to the next set of product types, then I want Excel to highlight these sets of rows a little darker version of the previous color. When the word changes in column A to yet another set of product types, I want Excel to highlight these sets of rows back to the original pastel color...and so on, so that the table rows are highlighted by product types, alternating btw. the two colors.

    I couldn't find this anywhere. Can anyone help?

    Thank you in advance for your help!

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    14,207

    Default Re: Highlight table rows different color for every change in word

    Quote Originally Posted by gertie193 View Post
    Hello,

    I am using Excel 2007.

    I have a big spreadsheet that has been sorted by column A, which is a list of product types--the words can vary, so there are no set words I can give you.

    I don't know how to write code, but I would like a VBA code that will highlight a table row (not an entire row) a certain pastel color for all products that have the same word in column A. When the word changes in column A to the next set of product types, then I want Excel to highlight these sets of rows a little darker version of the previous color. When the word changes in column A to yet another set of product types, I want Excel to highlight these sets of rows back to the original pastel color...and so on, so that the table rows are highlighted by product types, alternating btw. the two colors.

    I couldn't find this anywhere. Can anyone help?

    Thank you in advance for your help!
    Here's some code that will need tweaking to fit your needs. Here I assumed the table starts in cell A1, and has no blank rows. You can adjust the values of R1,G1,B1 and R2,G2,B2 to give you the pastel shades you want.
    Code:
    Sub TwoColorBands()
    Dim rng As Range
    
    Dim lastCol As Long, lastRw As Long, indx As Long, ct As Long
    Dim R1 As Integer, G1 As Integer, B1 As Integer
    Dim R2 As Integer, G2 As Integer, B2 As Integer
    
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    lastRw = Range("A" & Rows.Count).End(xlUp).Row
    R1 = 200
    G1 = 50
    B1 = 100
    R2 = 250
    G2 = 75
    B2 = 120
    indx = 0
    Set rng = Range("A1", Cells(lastRw, lastCol))
    rng.Rows(1).Interior.Color = RGB(R1, G1, B1)
    For Each rw In rng.Rows
        If rw.Cells(1).Value = rw.Cells(1).Offset(1, 0).Value Then
            rw.Offset(1, 0).Interior.Color = rw.Interior.Color
        ElseIf rw.Offset(1, 0).Row <= lastRw Then
            indx = indx + 1
            If indx Mod 2 <> 0 Then
                rw.Offset(1, 0).Interior.Color = RGB(R2, G2, B2)
            Else
               rw.Offset(1, 0).Interior.Color = RGB(R1, G1, B1)
            End If
        End If
    Next rw
    End Sub

  3. #3
    Board Regular
    Join Date
    Feb 2008
    Posts
    60

    Default Re: Highlight table rows different color for every change in word

    JoeMo,

    That worked perfectly!!! Thank you so much for answering my question.
    You have helped me tremendously at work.

    All the best.

    Sincerely,
    gertie193

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com