New Contributor

# Finding / marking errors with formula depending on two rows

Hello,

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.

Greetings

2 Replies

# Re: Finding / marking errors with formula depending on two rows

``````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

Else

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

Else
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.

# Betreff: Finding / marking errors with formula depending on two rows

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

``=COUNT(FILTER(\$B:\$B,\$A:\$A=\$A1))``

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:

``=IF(NOT(ISBLANK(\$A1)),COUNT(FILTER(\$B:\$B,\$A:\$A=\$A1))<>1)``

(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:

``=LET(Avalues,FILTER(UNIQUE(\$A:\$A),UNIQUE(\$A:\$A)<>""),numberofBvalues,MAP(Avalues,LAMBDA(a,COUNT(FILTER(\$B:\$B,(\$A:\$A=a))))),FILTER(Avalues,numberofBvalues<>1))``