Finding / marking errors with formula depending on two rows

New Contributor


I have attached an example file to understand my issue.

I want to use a formula who is able to detect if a number in row A has more than one number in row B.

Per each number in A only one result is allowed to be in B.
How can I mark them all red?


I also tried to create a function, which is looking for the same number in B, 5 rows above and 5 below.


We are talking about 12000 rows and my functions mostly went to an error.


Thank you so much in advance.



2 Replies


Sub red()

Dim i As Long
Dim entryinA As Long
Dim entriesinB As Long

Range("A:B").Interior.ColorIndex = -4142

For i = 1 To 12000

If Cells(i, 1).Value = Cells(i + 1, 1).Value Then

entryinA = entryinA + 1


entriesinB = Application.WorksheetFunction.CountA(Range(Cells(i - entryinA, 2), _
Cells(i, 2)))

If entriesinB > 1 Then

Range(Cells(i - entryinA, 1), Cells(i, 2)).Interior.ColorIndex = 3

End If

entryinA = 0

End If

Next i

End Sub

Maybe with these lines of code. In the attached file you can click the button in cell E2 to run the code.



you can count the number of values in col B for the value in A with the following formula:






 Put the formula in C1 and copy it down.


To mark all the erroneous rows red create a conditional formatting rule with the following formula:




(Create the rule in cell A1 and then change the range ("Manage rules…") to $A:$B)


Alternatively, you can list all the erroneous values of A: