Referencing a range from another sheet via VBA for Conditional Formatting?

[jmpatrick]

Good morning! I'm trying to learn how to create Conditional Formatting rules using VBA.

I have this simple code that is working:

Sub VBAConditionalFormatting()
 
  Dim MyRange As Range
  Set MyRange = Worksheets("Calendar").Range("SubLotColumn")
  
  MyRange.FormatConditions.Delete

  For Each cell In MyRange
  If cell.Value Like "Independence Day" Then
     cell.Interior.ColorIndex = 6

End If
Next
End Sub

In order to make it easier to add and delete holidays I'd like to change the If cell.Value Like "Independence Day" Then part to reference a range from another sheet. Something like this:

If cell.Value Like "Worksheets("Holidays").Range("Holidays")" Then

That way I can easily update the list of Holidays and the Conditional Formatting will never have to be touched.

Make sense?

 

[jmpatrick]

I tried this. No luck:

Sub VBAConditionalFormatting()
 
  Dim MyRange As Range
  Set MyRange = Worksheets("Calendar").Range("SubLotColumn")
 
  Dim MyData As Range
  Set MyData = Worksheets("Holidays").Range("Holidays")
 
  MyRange.FormatConditions.Delete

  For Each cell In MyRange
  If cell.Value Like MyData Then
     cell.Interior.ColorIndex = 6

End If
Next
End Sub