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 · Accepted Answer · 2020-03-05 20:51:33Z

3

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)

edited Mar 5, 2020 at 20:51

answered Mar 5, 2020 at 20:25

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Handreen Over a year ago

Oh I see, thanks for catching that I didn't notice the missing =. And that Countif statement also seems to do the trick with a simpler code, thanks for that as well! Is there any way I can add something simple to make the mismatched cells in Sheet 1 red? Something to include your suggestion slightly modified, so the formula would be something along the lines of Formula1:="=COUNTIF('Test 2'!$A:$A,A2)=0"

BigBen Over a year ago

Yes - that would be a separate rule.

Handreen Over a year ago

After .FormatConditions(1).Interior.Color = vbGreen I tried to add .FormatConditions.Add Type:=xlExpression, Operator:=xlExpression, Formula2:="=COUNTIF('Test 2'!$A:$A,A2)=0)" & .FormatConditions(2).Interior.Color = RGB(255, 199, 206) but it seems to be giving me an error, first time using conditional formatting in VBA so I apologize if this is not how you're supposed to add a separate rule. Do I just have to make a completely new With statement and include this, or can I add it to the current with statement?

BigBen Over a year ago

Remove the parenthesis after the 0.

BigBen Over a year ago

Funny - you had Formula1:="=COUNTIF('Test 2'!$A:$A,A2)=0" in your first comment so idk how that got changed to Formula2:="=COUNTIF('Test 2'!$A:$A,A2)=0)".

|

Collectives™ on Stack Overflow

Using VLookup with Conditional Formatting when Inserting a New Row

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related