vba Conditional Formatting based on formulas

Question

I want a simple color code on quite a large spreadsheet (several hundreds cells to color). If I use CF it slows the computer and Excel just crashes. I want to try and do it with VBA. I tried the below code but it only works if I type the value (being 1, 2 or 3). It doesn't work if the value if the result of a formula. Any idea?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icol As Integer, c As Range, rng As Range

If Target.Count > 1 Then Exit Sub

Set rng = Range("D2:s1000")

If Intersect(Target, rng) Is Nothing Then Exit Sub

For Each c In Intersect(Target, rng)

    Select Case UCase(c.Value)
        Case 1: icol = 3
        Case 2: icol = 4
        Case 3: icol = 18
        Case Else: icol = 0
    End Select
    c.Interior.ColorIndex = icol
Next c
End Sub

If Jean Francois Corbett could answer that would be great!

I'm not JFC, so maybe I'm not supposed to answer, but Worksheet_Change isn't triggered by a recalculation. You could try handling the Worksheet_Calculate event as well: it does require a bit more work, since there's no "Target" range to check in that event. — Tim Williams
– Tim Williams, Commented Jan 30, 2013 at 1:19

Dale M · Accepted Answer · 2013-02-19 03:57:55Z

1

@TimWilliams is correct, however, you can recurively expand the target range to include target.dependants like

Private Function TargetDependents(ByRef Target As Range) As Range
    Dim c As Range

    If Not Target.Dependents Is Nothing Then
        Set TargetDependents = Union(Target, Target.Dependents)
    End If

    If TargetDependents.Cells.Count > Target.Cells.Count Then
        TargetDependents = TargetDependents(TargetDependents)
    End If
End Function

and change this:

For Each c In Intersect(Target, rng)

to:

For Each c In Intersect(TargetDependents(Target), rng)

Update in response to comment, the edited code should look like this

Private Function TargetDependents(ByRef Target As Range) As Range
    Dim c As Range

    If Not Target.Dependents Is Nothing Then
        Set TargetDependents = Union(Target, Target.Dependents)
    End If

    If TargetDependents.Cells.Count > Target.Cells.Count Then
        TargetDependents = TargetDependents(TargetDependents)
    End If
End Function

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icol As Integer, c As Range, rng As Range

If Target.Count > 1 Then Exit Sub

Set rng = Range("D2:s1000")

For Each c In Intersect(TargetDependents(Target), rng)

    Select Case UCase(c.Value)
        Case 1: icol = 3
        Case 2: icol = 4
        Case 3: icol = 18
        Case Else: icol = 0
    End Select
    c.Interior.ColorIndex = icol
Next c
End Sub

edited Feb 19, 2013 at 3:57

answered Jan 30, 2013 at 4:04

Dale M

2,4831 gold badge15 silver badges22 bronze badges

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

8 Comments

Lionel Justafre Over a year ago

I just came back from leave and saw your replies. I'm sorry if I gave the impression that I understood the code I pasted above, I don't... which is why I have no clue how to include the changes you have kindly offered. If any of you could let me know how to get this code working that would be a tremendous help :)

Lionel Justafre Over a year ago

Thanks Dale, Now I get an error: it highlights NOTHING and says: Compile error: Invalid use of object.

Lionel Justafre Over a year ago

line 3: If Target.Dependents Is Not Nothing Then

Dale M Over a year ago

Too much vb.net! If Target.Dependents Is Not Nothing Then should be If Not Target.Dependents Is Nothing Then. Will edit

Lionel Justafre Over a year ago

Hi Dale, now it says Run-time error '1004' No cells were found. and the line you edited is highlighted if I press Debug.

|

Collectives™ on Stack Overflow

vba Conditional Formatting based on formulas

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related