Excel – In excel, cannot figure out why background color for a cell will not change

You didn't tag with your Excel version or provide any sample data, so let's say you are using Excel 2007 or greater, and your worksheet looks like this:

To compare the cells in column D to the corresponding cells in column A, do the following:

1. Select cells D2:D7 by clicking on cell D2 and then dragging down to cell D7, so that cell D2 is the Active Cell (as seen in the above screenshot)
2. On the Ribbon, click Home -> Conditional Formatting -> New Rule...
3. In the Select a Rule Type section of the New Formatting Rule dialog, click Use a formula to determine which cells to format
4. In the Format values where this formula is true textbox, enter the following formula: =D2<>A2
5. Click the Format button
6. In the Format Cells dialog, set your desired formatting (e.g. click the Fill tab, and select the Yellow color)
7. Click the OK button
8. In the New Formatting Rule dialog, click the OK button
9. Repeat the above steps, except select cells E2:E7, and use the formula =E2<>B2

Note:
Since the columns I am formatting are contiguous, I could have simply selected cells D2:E7 instead of D2:D7, and then skipped step 9. However, I am assuming that the columns you want to format are not necessarily contiguous.

Optional:
Since background-color formatting hides cell gridlines, one additional thing I like to do in the Format Cells dialog is to click the Border tab, select "White, Background 1, Darker 15%" in the Color dropdown, and then apply that color to the Outline borders. That way, your formatted cells will appear to retain their gridlines.

Result:

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.