Excel: Change cell colour if contents different from previous cell

vba Iran
  • 9 years ago

    Hello,

    I’m looking for a way to make the cells of a given row change background colour each time the cell contents differ from the preceding cell; I don’t mind if the colour changes to a new colour or simply toggles between two colours.  For example, if the cell contents are Tom; Tom; ***; Harry; Harry then I want “***” to be in a different colour from “Tom” and “Harry”.

    The logic of what I’m trying to achieve is, “Check if the contents of the current cell equals the contents of the previous cell.  If Yes, then make the background colour the same as that of the previous cell.  If No, then change the background colour.”

    I have a few hundred rows and a few hundred different possible cell contents and would like the colours to update each time a cell value is changed.

    Can anyone help, please?

    Many thanks in advance.

    Pieter C

  • 9 years ago

    To do this in code, you'll need something like this:

    Dim intRows As Integer
    Dim intCols As Integer

    'determine how many rows and columns to work on
        ActiveCell.SpecialCells(xlLastCell).Select
        intRows = Selection.Row
        intCols = Selection.Column
       
        intColour = 2

    For i = 2 To intRows
        For j = 2 To intCols
        'change colour of first cell on each row
        If j = 2 Then Cells(i, 1).Interior.ColorIndex = intColour
            Cells(i, j).Select
            'see if value same or different, then colour cell accordingly:
            If Cells(i, j).Value <> Cells(i, j - 1).Value Then
                intColour = (intColour + 1) Mod 55
                'avoid background colour being same as text (black in this case)
                If intColour = 1 Then intColour = 2
                Selection.Interior.ColorIndex = intColour
            Else
                Selection.Interior.ColorIndex = Cells(i, j - 1).Interior.ColorIndex
            End If
        Next j
    Next i

    End Sub

     

    However, this is quite slow if you have a lot of rows and columns, so you wouldn't want to trigger it every time a cell value is changed.

    Hope this helps!

  • 9 years ago

    Uncle - Thank you so much for replying so quickly, it's just what I was looking for.  I had a few problems with xlLastCell caused by Excel not resetting after cells were cleared, but a quick search provided a solution.  I liked your foresight in preventing black text on a black background and I'll now add in a few lines to turn text yellow for other dark backgrounds.

    Many thanks again.

     Pieter C

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Weeks of coding can save you hours of planning.”