Conditionally formatting ranges

Question

I have two ranges of data that I want to compare with and format if they match. So I want to format a range 1 cell if any of that data matches to the the data in range 2. This is what I have so far - it works until I change the data to range 2 but doesn't update it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRange As Range, cell As Range
Set myRange = Range("a9:a12")

For Each cell In myRange
If cell.Value = ActiveCell.Value And Not IsEmpty(ActiveCell.Value) Then
ActiveCell.Interior.ColorIndex = 3

End If
Next cell
End Sub

The problem is the cell still stays the colors that it was formatted from the first block of code so how can I change it back if the data in the second range gets changed?

  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myRange1 As Range
  Set myRange1 = Range("f9:f12")

  If Not Intersect(Target, Range("f1:f6")) Is Nothing Then
  If Application.WorksheetFunction.CountIf(myRange1,   ActiveCell.Value) > 0 _
  Then ActiveCell.Interior.ColorIndex = 3 Else ActiveCell.Interior.Color =   xlNone
  End If
End Sub

Siddharth Rout · Accepted Answer · 2015-09-28T20:52:49.403

3

Is this what you are trying?

If cell.Value = ActiveCell.Value And _
Not IsEmpty(ActiveCell.Value) Then
    ActiveCell.Interior.ColorIndex = 3
Else
    ActiveCell.Interior.Color = xlNone
End If

EDIT

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myRange As Range

    Set myRange = Range("a9:a12")

    If Application.WorksheetFunction.CountIf(myRange, ActiveCell.Value) > 0 _
    Then ActiveCell.Interior.ColorIndex = 3 Else ActiveCell.Interior.Color = xlNone
End Sub

EDIT

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange As Range
    Set myRange = Range("f9:f12")

    If Not Intersect(Target, myRange) Is Nothing Then
        If Application.WorksheetFunction.CountIf(myRange, Target.Value) > 0 _
        Then Target.Interior.ColorIndex = 3 Else Target.Interior.Color = xlNone
    End If
End Sub

edited Sep 28 '15 at 20:52

answered Sep 28 '15 at 16:52

Siddharth Rout

147,039
17
206
250

it's uncoloring it but i only need it to UNCOLOR if the data in the 2nd range range of data no longer matches with any data in the first. think of it like this it's like doing a vlookup and if the data in the first range matches it gets highlighted, if not it is uncolored. That is what i am trying to accomplish - my first IF does that but once i change (update) the data in the 2nd row it does not unhighlight it – exitleft Sep 28 '15 at 19:40
See the edit. You may have to refresh the page. Is that what you want? – Siddharth Rout Sep 28 '15 at 19:46
yes that works perfect - thank you! is there a way this procedure can execute as a user typing into the cell ? – exitleft Sep 28 '15 at 19:49
`is there a way this procedure can execute as a user typing into the cell ` Well yes but that would require subclassing and is slightly complex. But if you want the code to run once the user finishes typing and presses "Enter" then see [THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) – Siddharth Rout Sep 28 '15 at 19:51
BTW, instead of using `ActiveCell`, i would recommend using the relevant Cell Address. – Siddharth Rout Sep 28 '15 at 19:52
hey I am trying to use your code in the page you referred see original post edit but is not working as desired – exitleft Sep 28 '15 at 20:48
I have updated the post. You may have to refresh it to see it :) – Siddharth Rout Sep 28 '15 at 20:53
@Sidharth Rout that highlights range 2 when it changes but not range 1 e.g i have range f1:f6 and if i type a value that matches a value in range f9:f12 then the color of cell in range f1:f6 should also change atm it only changes color of range f9:f12...thank you for your help is really appriciated i think we're almost there – exitleft Sep 28 '15 at 21:02
Oh I see what you mean.. one moment please – Siddharth Rout Sep 28 '15 at 21:05
Change `If Not Intersect(Target, myRange) Is Nothing Then` to `If Not Intersect(Target, Range("f1:f6")) Is Nothing Then` – Siddharth Rout Sep 28 '15 at 21:06

score 0 · Answer 2 · 2015-09-28T20:35:11.687

You seem to be taking a somewhat inefficient route with your loop and are ignoring one of the tools (e.g. Target) that is being provided to you.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'important for _SelectionChange event macros
    'only process the cells to the extents of the data, not whole rows or columns
    If Not Intersect(Target, Target.Parent.UsedRange) Is Nothing Then
        Dim c As Range
        For Each c In Intersect(Target, Target.Parent.UsedRange)
            c.Interior.ColorIndex = 3 + _
                4145 * IsError(Application.Match(c.Value2, Range("A9:A12"), 0))
        Next c
    End If
End Sub

For a Worksheet_SelectionChange event macro, the Target represents one or more cells that is the current Selection. By cycling through each of the cells in the current selection, you can perform this pseudo-Conditional Formatting on a larger range. The Target or Selection can be any number of cells up to the total number of cells in a worksheet but the ActiveCell property can only ever be a single cell.

I've reduced the color on/color off switch to a single worksheet MATCH function and a little maths. This does away with looping through the criteria cells.

Because you may want to select entire row(s) or column(s) at some point, I've included a cell processing 'limit' that will process to the extents of the data on the worksheet. Without a cap on the cells to process, it is very easy to get caught up in the unnecessary processing of entire rows or columns of blank cells when using Worksheet_SelectionChange.

thanks for you answer i will give this a try, i am new to vbs so don't really know what i am doing :P — exitleft, Sep 28 '15 at 20:19

Conditionally formatting ranges

2 Answers2