Using VLookup with Conditional Formatting when Inserting a New Row

[dreen]

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

Please Note, cross-posted with: Using VLookup with Conditional Formatting when Inserting a New Row

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

 

[rondeondo]

Hi dreen

One option is to use a table for your data, any time you add a row the formatting and formulas will automatically be applied to the new row. There can be down sides but worth a try. With this you won't need to do anything else when adding a row.
As far as your code goes, you're adding conditional formatting based on cell A2. this will be looking at A2 for each cell rather than the relative cell. You'll need to use the row number you're in, maybe build a cell reference then use indirect wrapped around it to replicate the cell reference.

As an asside, when I'm looking to see if a value is present in another sheet I go for match wrapped in isnumber. but that may just be a preference thing.
eg =if(isnumber(match(a2,sheet2!$A:$A,0)),"Found","Not Found")
Match returns the line number which matches the value searched for. if it's not found it returns N/A. N/A will be a false in isnumber.

 

[dreen]

Hi dreen

One option is to use a table for your data, any time you add a row the formatting and formulas will automatically be applied to the new row. There can be down sides but worth a try. With this you won't need to do anything else when adding a row.
As far as your code goes, you're adding conditional formatting based on cell A2. this will be looking at A2 for each cell rather than the relative cell. You'll need to use the row number you're in, maybe build a cell reference then use indirect wrapped around it to replicate the cell reference.

As an asside, when I'm looking to see if a value is present in another sheet I go for match wrapped in isnumber. but that may just be a preference thing.
eg =if(isnumber(match(a2,sheet2!$A:$A,0)),"Found","Not Found")
Match returns the line number which matches the value searched for. if it's not found it returns N/A. N/A will be a false in isnumber.

Thanks for your suggestion Rondeondo!

The final code I ended up going with was:

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:="=COUNTIF('Test 2'!$A:$A,A2)>0"

            .FormatConditions(1).Interior.Color = RGB(198, 239, 206)
            
            .FormatConditions.Add Type:=xlExpression, Operator:=xlExpression, Formula1:="=COUNTIF('Test 2'!$A:$A,A2)=0"

            .FormatConditions(2).Interior.Color = RGB(255, 199, 206)
            
        End With

    End If
    
End Sub

 

[rondeondo]

so long as it does what you want it's all good.