[Solved] Conditional Formatting or VBA Code to change colors of cells based on value

bino1121

New member
I have 2 sheets one is a data set (Sheet1) and the other is a list of building names (Sheet2). The range of cells can vary in size for both sheets as these are reports that are generated and change from day to day. What I am trying to do is if a value from Sheet2 shows up in the dataset sheet1 I would like the color to change on SHEET2 to Red. Conversely, IF a building name does not show up on sheet1 then the color on sheet2 for that building name needs to change to green. I have tried Conditional formatting using this function

=iSERROR(VLOOKUP(A1,Sheet2!$A:$A, 1, 0)) = False color changes to green But this does not work it changes everything green

I need the values on sheet2 starting from A2 down to a variable range
and I need the values being compared from sheet2 to sheet1 to be a variable range on sheet one as this range could increase or decrease from time to time.
 
I have 2 sheets one is a data set (Sheet1) and the other is a list of building names (Sheet2). The range of cells can vary in size for both sheets as these are reports that are generated and change from day to day. What I am trying to do is if a value from Sheet2 shows up in the dataset sheet1 I would like the color to change on SHEET2 to Red. Conversely, IF a building name does not show up on sheet1 then the color on sheet2 for that building name needs to change to green. I have tried Conditional formatting using this function

=iSERROR(VLOOKUP(A1,Sheet2!$A:$A, 1, 0)) = False color changes to green But this does not work it changes everything green

I need the values on sheet2 starting from A2 down to a variable range
and I need the values being compared from sheet2 to sheet1 to be a variable range on sheet one as this range could increase or decrease from time to time.
Hello Bino1121

Thank you for contacting us. I understand you want to apply conditional formatting in Excel to highlight cells in Sheet2 based on whether or not they appear in Sheet1. Specifically, you want the appropriate cell in Sheet2 to be highlighted in red if a value from Sheet2 shows in Sheet1 and green if it does not occur in Sheet1. You also state that the cell ranges for both sheets can change in size, so the process must handle variable fields. Per your specifications, I can provide an Excel VBA code to fulfil your needs. The code presumes the building names from Sheets 1 and 2 are in column A. However, you can apply the formatting rules used in the code for Conditional Formatting manually.

Code:
Sub ChangeColorBasedOnValue()

    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=NOT(ISERROR(MATCH(A2,Sheet1!$A:$A,0)))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ISERROR(MATCH(A2,Sheet1!$A:$A,0))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
End Sub

To be more fluent with Conditional Formatting when applying any formula, follow the article link below.

Conditional Formatting with Formula in Excel

Regards
Lutfor Rahman Shimanto
 
I get an error: Invalid procedure call or argument debug highlights this line of the code.

Selection.FormatConditions.Add Type:=xlExpression, Formula:= _
"ISERROR(MATCH(A2,Sheet1!$A:$A,0))"
 
I get an error: Invalid procedure call or argument debug highlights this line of the code.

Selection.FormatConditions.Add Type:=xlExpression, Formula:= _
"ISERROR(MATCH(A2,Sheet1!$A:$A,0))"
Dear Bino1121

Greetings from our platform. The error you're getting could be because of the Formula1 argument of the FormatConditions.Add method. To make it valid, you must replace Formula with Formula1 in that line, as shown below.

Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=ISERROR(MATCH(A2,Sheet1!$A:$A,0))"

I recommend you copy the code instead of typing it. I will give you a modified version of the previous code where I fixed Sheet2 as an active sheet. If this code does not solve your issue, you may share your Workbook for better understanding.
Code:
Sub ChangeColorBasedOnValue()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2")
    ws.Activate
    
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=NOT(ISERROR(MATCH(A2,Sheet1!$A:$A,0)))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ISERROR(MATCH(A2,Sheet1!$A:$A,0))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
End Sub

Don't hesitate to inform us if you face any other issues, and good luck.

Regards
Lutfor Rahman Shimanto
 
Current Issues with the code I'm attempting to run. Sorry for the quality.
View attachment 240
Dear Bino1121

Thank you for explaining your issue with such clarity. The code we previously provided works perfectly on our end. The error message "Invalid procedure call or argument" typically occurs in VBA when you pass an incorrect argument to a function or method or try to call a non-existent way or property. I can resolve your issue with another approach. This time, I am providing VBA code that should be completable for all versions of Excel. You must run this code from the module after any changes occur, or you can create a suitable event and use the code body.

Code:
Sub ChangeColorBasedOnValueNew()

    Dim lastRowSheet1, lastRowSheet2 As Integer
    Dim i, j As Integer
    Dim tempName As String
    Dim found As Boolean
 
    lastRowSheet1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    lastRowSheet2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
 
    For i = 2 To lastRowSheet2
        tempName = Sheets("Sheet2").Cells(i, 1).Value
    
        If Sheets("Sheet2").Cells(i, 1).Value <> "" Then
            found = False
        
            For j = 2 To lastRowSheet1
                If Sheets("Sheet1").Cells(j, 1).Value <> "" Then
                    If tempName = Sheets("Sheet1").Cells(j, 1).Value Then
                        Sheets("Sheet2").Cells(i, 1).Interior.Color = vbRed
                        found = True
                        Exit For
                    End If
                End If
            Next j
        
        End If
    
        If Not found And Sheets("Sheet2").Cells(i, 1) <> "" Then
            Sheets("Sheet2").Cells(i, 1).Interior.Color = vbGreen
        End If
    
    Next i
 
End Sub

Good luck. Feel free to contact us again with any other inquiries or concerns.

Regards
Lutfor Rahman Shimanto
 

Online statistics

Members online
0
Guests online
11
Total visitors
11

Forum statistics

Threads
287
Messages
1,240
Members
508
Latest member
HaroldDyeme
Top