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
精彩评论