VBA Highlight Matching Records

Contents hide

Problem Description:

I wanted to know how to apply conditional formatting rules using vba…

I have the following dataset:

As you can see the first column is the UNIQUE_ACCOUNT_NUMBER which consists usually in two matching 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 isnt(for the two matching records) then I want to highlight it yellow.

For example:

As you can see the account number MTMB^1^10000397 was matching twice but the Arrears_flag wasnt matching so i want to highlight it yellow.

I hope this makes sense.

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

Thanks!

Solution – 1

I managed to get it working, adding a new helper column, concatenating the account number and the "ACC_HEADER_POOL" column in column "I", and using =COUNTIF(I$2:I$5,I2)=1 as the formula on which the conditional formatting is based, as you can see in this screenshot:

Solution – 2

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

Rate this post