Excel Conditional Formatting Using A Reference Range

I have a watch range with certain cells highlighted yellow. For each of these cells, there is a corresponding reference value all located within 开发者_运维问答one column.

I want to highlight all cells in my target range red for which the value in the corresponding reference cell matches the value in the target cells.

The code I've come up with is as follows, but there is some sort of a compile error I am unable to fix. Apparently the watch range can't consist of "multiple ranges".

Sub Highlight_pairAB()
Dim WatchRange As Range, Target As Range, cell As Range, ref As Range
Set Target = Range("Y3:Y274", "AC3:AC274") 'change column ref as required
Set WatchRange = Range("B3:B274", "E3:E274", "H3:H274", "K3:K274")
Set RefRange = Range("A3:A102")

For Each cell In Target.Cells
    If Application.WorksheetFunction.CountIf(WatchRange, cell.Value) > 0 Then
        cell.Interior.ColorIndex = 3
        For Each watchCell In WatchRange.Cells
            If watchCell.Interior.ColorIndex = 6 And RefRange.Value = Target.Value Then: targetCell.Interior.ColorIndex = 3
        Next watchCell
    Else: cell.Interior.ColorIndex = xlNone
    End If
Next cell
End Sub

---

Just in case this helps: Change:

Set WatchRange = Range("B3:B274", "E3:E274", "H3:H274", "K3:K274") 

To:

Set WatchRange = Range("B3:B274,E3:E274,H3:H274,K3:K274")

---

Another thing you could try would be to replace

For Each watchCell In WatchRange.Cells

with

For Each area In WatchRange.Areas
     For Each watchCell In area.Cells

edit: you also need two "Next" statements for it to match up. So be sure to make it

    Next watchCell
Next area