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
    



</details>


# 答案1
**得分**: 0

以下是代码的中文翻译部分：

```vba
在Sheet1的模块中尝试放置以下代码：

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

   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
```

请注意，这是您提供的VBA宏代码的中文翻译，其中包括了一些Excel工作表的更改事件处理。

<details>
<summary>英文:</summary>

try put in sheet1 module

   Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.Cells.CountLarge &gt; 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(&quot;Sheet2&quot;).UsedRange.Find(what:=Target.Value, Lookat:=xlWhole)
       
       If Not found Is Nothing Then
   
      Set COND1 = Target.FormatConditions.Add(xlCellValue, xlEqual, &quot;Y&quot;)
      Set COND2 = Target.FormatConditions.Add(xlCellValue, xlEqual, &quot;TBC&quot;)
      Set COND5 = Target.FormatConditions.Add(xlTextString, TextOperator:=xlBeginsWith, String:=&quot;NO SHOW&quot;)
      Set COND6 = Target.FormatConditions.Add(xlTextString, TextOperator:=xlBeginsWith, String:=&quot;Replacement&quot;)
      
       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

</details>