CODEWITHSUNDEEP
excelvba
MJobbson
MJobbson

Reputation: 169

Pointers requested for a conditional formatting / VBA solution in excel

I've been set the task of finding a solution to the following problem. I need to find a way of checking to see if an entered cell value appears on another sheet within the same workbook. for example enter data in sheet1 -- check if value appears in sheet2 -- conditionally format entered value in sheet 1. I've found solutions in the past to use VBA to use the 'condition' option to format a cell based on its entered value but never have i tried to reference that value against another sheet. So i'm a little lost. Any pointers down a possible path to a solution would be great


           Dim RG As Range
    Dim COND1 As FormatCondition
   Set RG = Sheet8.Range("WEDAYBOXES")
   
    Dim RG1 As Range
    Dim COND2 As FormatCondition
    Dim COND5 As FormatCondition
    Dim COND6 As FormatCondition
    Set RG1 = Sheet8.Range("Y46:MY145")
   RG.FormatConditions.Delete
     RG1.FormatConditions.Delete
     
    Set COND1 = RG.FormatConditions.Add(xlCellValue, xlEqual, "Y")
     With COND1
    .Interior.Color = RGB(146, 208, 80)
    End With
    
     Set COND2 = RG1.FormatConditions.Add(xlCellValue, xlEqual, "TBC")
   Set COND5 = RG1.FormatConditions.Add(xlTextString, TextOperator:=xlBeginsWith, String:="NO SHOW")
   Set COND6 = RG1.FormatConditions.Add(xlTextString, TextOperator:=xlBeginsWith, String:="Replacement")
   
     With COND2
    .Interior.Color = vbRed
    .Font.Color = vbWhite
    End With
    With COND5
    .Interior.Color = vbRed
    .Font.Color = vbYellow
    End With
     With COND6
    .Interior.Color = vbYellow
    .Font.Color = vbRed
    End With

Upvotes: 0

Views: 42

Answers (1)

k1dr0ck
k1dr0ck

Reputation: 1215

try put in sheet1 module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    
    Dim found As Range
    Dim COND1 As FormatCondition
    Dim COND2 As FormatCondition
    Dim COND5 As FormatCondition
    Dim COND6 As FormatCondition
    
    Application.EnableEvents = False
    
    Set found = Sheets("Sheet2").UsedRange.Find(what:=Target.Value, Lookat:=xlWhole)
    
    If Not found Is Nothing Then

   Set COND1 = Target.FormatConditions.Add(xlCellValue, xlEqual, "Y")
   Set COND2 = Target.FormatConditions.Add(xlCellValue, xlEqual, "TBC")
   Set COND5 = Target.FormatConditions.Add(xlTextString, TextOperator:=xlBeginsWith, String:="NO SHOW")
   Set COND6 = Target.FormatConditions.Add(xlTextString, TextOperator:=xlBeginsWith, String:="Replacement")
   
    With COND1
            .Interior.Color = RGB(146, 208, 80)
    End With
    With COND2
            .Interior.Color = vbRed
            .Font.Color = vbWhite
    End With
    With COND5
             .Interior.Color = vbRed
             .Font.Color = vbYellow
    End With
    With COND6
             .Interior.Color = vbYellow
             .Font.Color = vbRed
    End With
    
    End If
    
    Application.EnableEvents = True

End Sub

Upvotes: 0

Related Questions