Using VLookup with Conditional Formatting when Inserting a New Row

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52
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:

VBA Code:
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:

Code:
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!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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:

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top