Change cell color when a different value is encountered

Hello there. I Inserted an SQL query in Excel to pick for me data like the above.

Is there a way I can set Excel to automatically Change The Background color each time it encounter a different value. For example, we have Kigongo Charles in Orange, I want that when it meets the next value that is Not Kigongo Charles, it changes the Background color to any other color. ie when it reaches KITAYIMBWA ROBERT, it changes, then when it reaches MBULABANTU LAWRENCE, it changes again to any random color. Like that Like that. Am using Excel 2016

Any work around will be highly appreciated

Solution 1:

You might be interested in this Macro. I think it does exactly what you want now.

Sub ColorIndex()
    Dim x As Integer
    Dim y As Integer
    Dim lRows As Long
    Dim lColNum As Long
    Dim iColor As Integer
    Dim iDupes As Integer
    Dim bFlag As Boolean

    lRows = Selection.Rows.Count
    lColNum = Selection.Column
    iColor = 2

    For x = 2 To lRows
        bFlag = False
        For y = 2 To x - 1
            If Cells(y, lColNum) = Cells(x, lColNum) Then
                bFlag = True
                Exit For
            End If
        Next y
        If Not bFlag Then
            iDupes = 0
            For y = x + 1 To lRows
                If Cells(y, lColNum) = Cells(x, lColNum) Then
                    iDupes = iDupes + 1
                End If
            Next y
            If iDupes > 0 Then
                iColor = iColor + 1
                If iColor > 56 Then
                    MsgBox "Too many duplicate companies!", vbCritical
                    Exit Sub
                End If
                Cells(x, lColNum).Interior.ColorIndex = iColor

                For i = 1 To 5
                    Cells(x, lColNum + i).Interior.ColorIndex = iColor
                Next i

                For y = x + 1 To lRows
                    If Cells(y, lColNum) = Cells(x, lColNum) Then
                        Cells(y, lColNum).Interior.ColorIndex = iColor
                            For i = 1 To 5
                                Cells(y, lColNum + i).Interior.ColorIndex = iColor
                            Next i
                    End If
                Next y
            End If
        End If
    Next x
End Sub

I have edited the code to also make the color changes on the horizontal lines up to 5 cells away from the selected cell. So what you do is select all the names of the database and then run the macro.