Excel Conditional Formatting Using A Reference Range

Question

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

Crispy · Accepted Answer · 2009-10-14 21:50:49Z

1

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")

answered Oct 14, 2009 at 21:50

Crispy

5,6473 gold badges32 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jakber · Accepted Answer · 2009-10-15 20:09:11Z

1

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

edited Oct 15, 2009 at 20:09

answered Oct 14, 2009 at 22:00

jakber

3,57923 silver badges20 bronze badges

1 Comment

e9. Over a year ago

This looks promising but now I get an error of "Else without If" here: "Else: cell.Interior.ColorIndex = xlNone"

Collectives™ on Stack Overflow

Excel Conditional Formatting Using A Reference Range

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related