Change Background Color When Value In Column Changes

  • Hello,

    I'm trying to find a way to use VBA to apply background color to my worksheet based on the value in Column A. The values start in cell A2. I would like that whole row to be light grey, then continue applying a light grey background until I reach a new value. Then no background until the value changes again, then back to light grey. It would alternate until it reaches a blank cell.

    I attached an example. The tab named "P2 Expense Report (2)" is the one I need to color. The tab named "P2 Expense Report with Color" illustrates how I want my file to look after color is applied.

    Thanks for your help!

  • Re: Change Background Color When Value In Column Changes

    See how it helps.
    It works for the active sheet

  • Re: Change Background Color When Value In Column Changes

    Another variation to be able to launch again the macro without to remove previous format.
    A conditional format is used as demonstration in another sheet but is needed an extra column to make the row status.

  • Re: Change Background Color When Value In Column Changes

    Thanks PCI! I'm going with your second answer - it works perfectly! :)

    And thanks for posting the sample of how to do it with conditional formatting. I prefer the code for the spreadsheet this needs to generate, but I wouldn't have understood how to apply conditional formatting (in this case) without your example.

  • Hi PCI,

    Can you please update this so that it uses a different color in the color index? The way it is working now is that it alternates between gray to white. I am trying to make it change to a different color everytime the value changes all the way down a column.

    This works except for the color index:

    1. Option Explicit
    2. Sub Color_Row()
    3. Dim LastRow As Long
    4. Dim ColorFlag As Boolean
    5. Dim I As Long
    6. LastRow = Range("A" & Rows.Count).End(xlUp).Row
    7. ColorFlag = False
    8. For I = 2 To LastRow
    9. If (Cells(I, "A") <> Cells(I - 1, "A")) Then ColorFlag = Not (ColorFlag)
    10. If (ColorFlag) Then
    11. With Rows(I).Interior
    12. .ColorIndex = 15
    13. .Pattern = xlSolid
    14. .PatternColorIndex = xlAutomatic
    15. End With
    16. End If
    17. Next I
    18. End Sub
  • I strongly suggest that you read our Forum Rules, you have already ignored two of them:

    1.Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Just highlight all of the code and press the <> in the post menu above button to add the code tags.

    2.Please start your own post. Posting in another member's Thread is known as hijacking and is not allowed here. By all means add a link to a Thread that may be related to your question.

    When you have read the Rules then start your own question, this one is 12 years old

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!