VBA How to select values when cell beside it is empty and compare those values with another column in another sheet and highlight

Question

I am just learning vba in excel. I am stuck with a problem here.

What i am trying accomplish here is

Select values in Sheet1 ColumnB when Sheet1 ColumnC is empty.
Take those values and compare them with sheet2 ColumnO.
Sheet1 ColumnB values is green in sheet1 when common with sheet2 ColumnO values.
Sheet1 ColumnB values is yellow in sheet1 when it is not common with sheet2 ColumnO values.
sheet2 ColumnO values is red in sheet2 when its not in sheet1.

here is the sample file

https://a.uguu.se/tkGV68TzIGwR_testtt111.xlsx

This is what i got so far. I can select the values in ColumnB when columnC is empty and copy them in to another column.

Sub compareWR()
Dim emptyI As Long          
Dim emptyJ As Long          
lastrow = ws1.Cells(Rows.Count, "B").End(xlUp).Row
emptyJ = 2
For emptyI = 2 To lastrow
If Cells(emptyI, "C").Value = "" Then
Cells(emptyJ, "AA").Value = Cells(emptyI, "B").Value
        emptyJ = emptyJ + 1
End If
Next emptyI
End Sub

"Take those values and compare them with sheet2 ColumnO." - Do you mean anywhere in that column? Or same row, in that column? — BruceWayne
It looks like you've only tried #1 so far. If you try #2-5 first and tell us where you get stuck I think you'll get more answers. — puzzlepiece87

Unknown Unknown · Accepted Answer · 2017-08-02T17:17:48

It seems like some conditional formatting rules should take care of everything you require. Here they are in VBA.

Option Explicit

Sub CFRs()
    Dim addr1 As String, addr2 As String
    With Worksheets("sheet1")
        With .Range(.Cells(1, "B"), .Cells(.Rows.Count, "B").End(xlUp))
            addr1 = .Cells(1).Address(False, True)
            addr2 = .Cells(1).Offset(0, 1).Address(False, True)
            .FormatConditions.Delete
            With .FormatConditions.Add(Type:=xlExpression, _
              Formula1:="=AND(" & addr2 & "=TEXT(,), ISNUMBER(MATCH(" & addr1 & ", Sheet2!$O:$O, 0)))")
                .Interior.ColorIndex = 10
            End With
            With .FormatConditions.Add(Type:=xlExpression, _
              Formula1:="=NOT(LEN(" & addr2 & "))")
                .Interior.ColorIndex = 6
            End With
        End With
    End With
    With Worksheets("sheet2")
        With .Range(.Cells(1, "O"), .Cells(.Rows.Count, "O").End(xlUp))
            addr1 = .Cells(1).Address(False, True)
            .FormatConditions.Delete
            With .FormatConditions.Add(Type:=xlExpression, _
              Formula1:="=ISNA(MATCH(" & addr1 & ", Sheet1!$B:$B, 0))")
                .Interior.ColorIndex = 3
            End With
        End With
    End With
End Sub

VBA How to select values when cell beside it is empty and compare those values with another column in another sheet and highlight

1 Answers