Using VLookup with Conditional Formatting when Inserting a New Row

Question

I have two sheets and I am trying to re-apply the conditional formatting to Sheet 1 (Test 1) whenever a row is inserted into Row 2 of Sheet 1 (I have made my code apply the conditional formatting whenever Cell A2 is changed).

For the conditional formatting, I want to check if each visible cell in Column A Sheet 1 exists in Column A Sheet 2 with a Vlookup, and if it does then apply a Green conditional format to it.

I have two different codes I am trying to use in Sheet 1 ("Test 1") and both of them are applying the conditional format with the formulas, but none of the cells in Sheet 1 Column A are turning green when meeting the condition I have set with the formula.

Here are both of my codes, I only need one to work, just different formulas:

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Address = "$A$2" Then

    Dim lr As Long

    lr = Range("A" & Sheet4.rows.Count).End(xlUp).Row

        With Range("A2:A" & lr)

            .FormatConditions.Delete

            .FormatConditions.Add Type:=xlExpression, Formula1:="IF(ISLBANK(Vlookup(A2,'Test 2'!$A:$B,1,False)),TRUE,FALSE)"

            .FormatConditions(1).Interior.Color = vbGreen

        End With

    End If

End Sub

And the second Formula I have also tried is:

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Address = "$A$2" Then

    Dim lr As Long

    lr = Range("A" & Sheet4.rows.Count).End(xlUp).Row

        With Range("A2:A" & lr)

            .FormatConditions.Delete

            .FormatConditions.Add Type:=xlExpression, Operator:=xlExpression, Formula1:="Not(ISERROR(Vlookup(A2,'Test 2'!$A:$B,1,False)))"

            .FormatConditions(1).Interior.Color = vbGreen

        End With

    End If

End Sub

Cross-posted with: https://www.mrexcel.com/board/threads/using-vlookup-with-conditional-formatting-when-inserting-a-new-row.1126560/

I will update both posts if I get an answer, thank you!

BigBen BigBen · Accepted Answer · 2020-03-05T20:25:29

The second snippet is close, you are missing an = before the Not:

Formula1:="=Not(ISERROR(Vlookup(A2,'Test 2'!$A:$B,1,False)))"

Note the quotes around the entire formula in the screenshot below, which is what you currently have:

A simpler formula to use could be the following:

Formula1:="=COUNTIF('Test 2'!$A:$A,A2)>0"

EDIT: Based on comments, adding a second rule could look like this:

.FormatConditions.Add Type:=xlExpression, Operator:=xlExpression, Formula1:="=COUNTIF('Test 2'!$A:$A,A2)>0"
.FormatConditions.Add Type:=xlExpression, Operator:=xlExpression, Formula1:="=COUNTIF('Test 2'!$A:$A,A2)=0"

.FormatConditions(1).Interior.Color = vbGreen
.FormatConditions(2).Interior.Color = RGB(255, 199, 206)

Using VLookup with Conditional Formatting when Inserting a New Row

1 Answers