Highlight non-matching data

Question

How do I apply conditional formatting rules using VBA?

I have the following dataset:

The first column is the UNIQUE_ACCOUNT_NUMBER which is usually in two records, followed by other columns showing data related to the account numbers.

I want to apply a conditional formatting rule that if UNIQUE_ACCOUNT_NUMBER is matching, but any other column isn't (for the two matching records) then highlight it yellow.

For example:

The account number MTMB^1^10000397 was matching twice but the Arrears_flag wasn't matching so I want to highlight it yellow.

In this example I can only apply the match & Mismatch for one column.

Dim rg1 As Range
Set rg1 = Range("E3", Range("E3").End(xlDown))
Dim uv As UniqueValues
Set uv = rg1.FormatConditions.AddUniqueValues

uv.DupeUnique = xlDuplicate
uv.Interior.Color = vbRed

Answer 1

Please find the answer

Sub actin()


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For i = 2 To Sheet1.Cells(Rows.Count, "E").End(xlUp).Row


mrow = 0
If Application.WorksheetFunction.CountIf(Sheet1.Range(Sheet1.Cells(1, "E"), Sheet1.Cells(i - 1, "E")), Sheet1.Cells(i, "E")) > 0 Then
mrow = Application.WorksheetFunction.Match(Sheet1.Cells(i, "E"), Sheet1.Range(Sheet1.Cells(1, "E"), Sheet1.Cells(i - 1, "E")), 0)
End If


If mrow = 0 Then GoTo eee

If Sheet1.Cells(i, "G") <> Sheet1.Cells(mrow, "G") Then
Sheet1.Cells(i, "G").Interior.Color = vbYellow
Sheet1.Cells(mrow, "G").Interior.Color = vbYellow
End If

If Sheet1.Cells(i, "H") <> Sheet1.Cells(mrow, "H") Then
Sheet1.Cells(i, "H").Interior.Color = vbYellow
Sheet1.Cells(mrow, "H").Interior.Color = vbYellow
End If


eee:
Next i



Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub