Excel VBA don't chage cell color

Question

I need to understand why my code don't riesce to modify background color of cell (and I don't want use conditional formatting)

Function myCheck(ToVerify As Range, RightValue As Range) As Boolean
    Dim rng1 As Range
    Dim rng2 As Range

    For Each rng1 In ToVerify.Cells
        For Each rng2 In RightValue.Cells       
            If (rng1.Value <> rng2.Value) Then
                rng1.Interior.Color = RGB(255, 0, 0)
                'rng1.Cells.Interior.Color = RGB(0, 255, 0)
                'rng1.Cells.Interior.ColorIndex = 10
                'rng1.Interior.ColorIndex = 10
            End If
        Next rng2
    Next rng1

    SignIfError = True

End Function

I have made this code to determine which cells in a row (ToVerify) don't have same value of specific cell (RightValue). I need to make visible this cells, then I thought to change their background color. The problem is that Excel don't change the background color of this cell. I'm sure that the cells value satisfies the condition into If statement. The lines into IF statement that are commented are my other attempts, but none of these worked.

where am I doing wrong?

Answer 1

I'm going to assume RightValue is just one value, not a whole range of them. In that case, this might do what you want:

Sub myCheck(ToVerify As Range, RightValue As Variant)
Dim cell As Range

For Each cell In ToVerify.Cells
    If cell.Value <> RightValue Then
        cell.Interior.Color = RGB(255, 0, 0)
    'else make it white
    Else
        cell.Interior.Color = RGB(255, 255, 255)
    End If
Next cell
End Sub

Note that I've changed it from a Function to a Sub. As you don't seem to need a returned value. I also got rid of the SignIfError = True at the end, as that's only needed for a Function.

You could call it like this:

myCheck ActiveSheet.Range("A1:A5"), 3

If you want to call using a value in a cell for RightValue, you'd call it like this:

myCheck ActiveSheet.Range("A1:A5"), ActiveSheet.Range("B1").Value