Lookup & Color cell Red

[Danny69]

Hi,


The Result sheet column B & F to lookup in Sheet1 Column A & C if both condition are matched then color the cell Red in Result Sheet.


Help me find a solution.


Sample file attached.


Thanks in advance.

[arnelgp]

if you wish, you can fetch it here:
https://www.dropbox.com/scl/fi/tv2ib8eblp94yivde3mmq/Sample-Data.xlsx?dl=0&rlkey=vdy71abiwjsz6fmkq8gxc6q1l

[Danny69]

if you wish, you can fetch it here:
https://www.dropbox.com/scl/fi/tv2ib8eblp94yivde3mmq/Sample-Data.xlsx?dl=0&rlkey=vdy71abiwjsz6fmkq8gxc6q1l


HI,

Very nice, impressive, Please explain Red rows method.

Thanks.

[arnelgp]

as you can see i added concat columns to the right on both sheets.
the result sheet has one additional column (CountIfs())

use Conditional format (see on ribbon->format->conditional format) to
turn the font white on red when the column (countifs()) is not 0.

[p45cal]

You need to be a bit more careful.
arnelgp's solution does not highlight row 17 of the Result sheet when there is a match on row 17 of Sheet1 (also on row 18 (and that's the problem)).
There are several other rows it doesn't highlight, all because there is more than one match. A simple change to arnelgp's conditional format from:
=$N2=1
to:
=$N2>0
or even:
=$N2
will fix that.

But there is a formula which needs no helper columns; you're using a version of Excel after Excel 2007 so you have the COUNTIFS function available to you, so make that conditional format formula:
=COUNTIFS(Sheet1!$A:$A,$B2,Sheet1!$C:$C,$F2)>0

There is an additional robustness with this formula compared to concatenating columns. It doesn't apply in this particular instance (because all the State IDs are 3 digits exactly) but a concatenation of, for example:
23 and 4567
is the same as
234 and 567
which would give a match when there was none.
(This can be overcome by concatenating with an extra symbol between the values, eg:
=A2 & "¬" & C2 on Sheet1 and
=B2 & "¬" & F2 on sheet Result)

(Apart from all that, did you notice there was a major difference between the 2 matches for the S ID 006 and the Idt 10017 on the Result sheet?)

[Paul_Hossler]

This seems to be quick and catch the duplicates (e.g. row 17)



Option Explicit


Sub ColorRed()
Dim SheetA As Variant, SheetC As Variant, ResultB As Variant, ResultF As Variant
Dim i As Long, j As Long
Dim ws1 As Worksheet, wsResult As Worksheet

Set ws1 = Worksheets("Sheet1")
Set wsResult = Worksheets("Result")


With Application.WorksheetFunction
SheetA = .Transpose(Row1toEnd(ws1.Cells(1, 1)))
SheetC = .Transpose(Row1toEnd(ws1.Cells(1, 3)))
ResultB = .Transpose(Row1toEnd(wsResult.Cells(1, 2)))
ResultF = .Transpose(Row1toEnd(wsResult.Cells(1, 6)))
End With

For i = LBound(ResultB) + 1 To UBound(ResultB)

Application.StatusBar = "Result row " & i

For j = LBound(SheetA) + 1 To UBound(SheetA)

If (ResultB(i) = SheetA(j)) And ResultF(i) = SheetC(j) Then
wsResult.Cells(i, 2).Interior.Color = vbRed
wsResult.Cells(i, 6).Interior.Color = vbRed

ws1.Cells(j, 1).Interior.Color = vbRed
ws1.Cells(j, 3).Interior.Color = vbRed
End If
Next j
Next i

Application.StatusBar = False

MsgBox "Done"


End Sub




Private Function Row1toEnd(r As Range) As Range
Dim r1 As Range, r2 As Range

Set r1 = r.Cells(1, 1)
Set r2 = r1.End(xlDown)
Set Row1toEnd = Range(r1, r2)


End Function

[Danny69]

Hi,

Thanks to all.
Thanks a lot.