VBA to highlight cells if it's not matching the value of the corresponding cells in another column

[Bamh1]

Hello,

Suppose we are comparing Columns A and C. I want values of cells in Column A highlighted if not matching the corresponding value in Column C. I am thinking something like this:

Sub highlight_cells_if_not_matching ()

Dim i as Integer
Dim j as integer
Dim nr as integer

nr=selection.rows.count

For i =nr to 1 step -1
if selection.cells(i,j).value<>selection.cells.offset (i, j+2).value Then ' this is where I have problem, I am also thinking of using smth like [isna.worksheet.function(match (
selecion.cells(i, j).interior.color=vbcyan
end if
i=i+1
Next
End sub

Could someone please help me fixing this code.

Thank you,

Shawn

 

[HongRu]

Try.

Sub highlight_cells_if_not_matching()

    Dim i As Integer
    
    Dim nr As Integer
    
    nr = Selection.Rows.Count
    
    For i = nr To 1 Step -1
        If Selection.Cells(i, 1).Value <> Selection.Cells(i, 1).Offset(0, 2).Value Then
            Selection.Cells(i, 1).Interior.Color = vbCyan
        End If
    Next i
End Sub

 

[Peter_SSs]

If the values in the Selection are not the result of formulas, you could consider this method of doing them all at once.

BTW, any reason why you wouldn't just use standard Conditional Formatting for this?

Sub Diff()
  Dim a As Variant
  
  With Selection
    a = .Value
    .Value = Evaluate("if(" & .Address & "=" & .Offset(, 2).Address & ","""",1)")
    On Error Resume Next
    .SpecialCells(xlConstants, xlNumbers).Interior.Color = vbCyan
    On Error GoTo 0
    .Value = a
  End With
End Sub

 

[Bamh1]

Try.

Sub highlight_cells_if_not_matching()

    Dim i As Integer
   
    Dim nr As Integer
   
    nr = Selection.Rows.Count
   
    For i = nr To 1 Step -1
        If Selection.Cells(i, 1).Value <> Selection.Cells(i, 1).Offset(0, 2).Value Then
            Selection.Cells(i, 1).Interior.Color = vbCyan
        End If
    Next i
End Sub

Thank you so much HongRu for fixing the code. It worked.

 

[Bamh1]

If the values in the Selection are not the result of formulas, you could consider this method of doing them all at once.

BTW, any reason why you wouldn't just use standard Conditional Formatting for this?

Sub Diff()
  Dim a As Variant
 
  With Selection
    a = .Value
    .Value = Evaluate("if(" & .Address & "=" & .Offset(, 2).Address & ","""",1)")
    On Error Resume Next
    .SpecialCells(xlConstants, xlNumbers).Interior.Color = vbCyan
    On Error GoTo 0
    .Value = a
  End With
End Sub

Thank you very much, Peter. This works too. I have not tried conditional formatting on two columns. the for loop solution was the first thing that came to mind, and I know it's not fastest way in terms of processing time.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, does this really need to rely on the user's 'Selection' or is it certain fixed columns?