Conditional Formatting

[bino1121]

I have a data set on sheet1 and a list of colors on sheet2 I need a way to make it so that if a word/num (value) is present on sheet1 then the same value on sheet2 needs to highlight green and if the value is not present on sheet1 then the value on sheet two turns red... I've written 3 different VBA macros to try to accomplish this but it has not worked for any of them. I also tried =iSERROR(VLOOKUP(A1,Sheet2!$A:$A, 1, 0)) = False color changes to green But this does not work it changes everything green as a formula in conditional formatting and nothing any help would be greatly appreciated

 

[Fluff]

Hi & welcome to MrExcel.
For Green try

=MATCH(A1,Sheet1!$A:$A,0)

 

[bino1121]

Hi & welcome to MrExcel.
For Green try

=MATCH(A1,Sheet1!$A:$A,0)

Negative does not seem to change colors even when value is in list.

 

[bino1121]

I have a data set on sheet1 and a list of colors on sheet2 I need a way to make it so that if a word/num (value) is present on sheet1 then the same value on sheet2 needs to highlight green and if the value is not present on sheet1 then the value on sheet two turns red... I've written 3 different VBA macros to try to accomplish this but it has not worked for any of them. I also tried =iSERROR(VLOOKUP(A1,Sheet2!$A:$A, 1, 0)) = False color changes to green But this does not work it changes everything green as a formula in conditional formatting and nothing any help would be greatly appreciated

Ideally I would like this to be accomplished in VBA code so that I can apply it to multiple reports in the future...

This is one of the VBA Codes I tried to run:

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

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

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

 

[Fluff]

Negative does not seem to change colors even when value is in list.

In that case can you post some sample data from both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.