Thread: Multiple VB Conditional Formats for Numerical Ranges

I would like to change the background color if the value in the cell is
between a certain numerical range to create a color map. I've got some basic
VB code, but I cannot figure out how to 'execute' the code to update all the
background colors based on values and formulas already populated in the cell.
If I manually type a value in the cell and hit return, the code seems to
work.

How do I 'execute' or 'run' the code using existing cell values/formulas?
There isn't a "run" button to execute the code.

Here's what I have currently using other peoples sample codes:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("D37:AA62")) Is Nothing Then
'If Not Intersect(Target, Range("E107:E118")) Is Nothing Then
With Target
Select Case Target.Value
Case -13.8 To -11#
Target.Interior.ColorIndex = 41
Case -10.9 To -8.5
Target.Interior.ColorIndex = 33
Case -8.4 To -6#
Target.Interior.ColorIndex = 37
Case -5.9 To -3.5
Target.Interior.ColorIndex = 42
Case -3.4 To -1#
Target.Interior.ColorIndex = 34
Case -0.9 To 1.5
Target.Interior.ColorIndex = 38
Case 1.6 To 4#
Target.Interior.ColorIndex = 4
Case 4.1 To 6.5
Target.Interior.ColorIndex = 35
Case 6.6 To 9#
Target.Interior.ColorIndex = 6
Case 9.1 To 11.5
Target.Interior.ColorIndex = 40
Case 11.6 To 14#
Target.Interior.ColorIndex = 45
Case Is >= 14.1
Target.Interior.ColorIndex = 3

End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

Simplest way is to just select them all and then just do an F2 then Enter
for each cell.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"kazoo" <kazoo@discussions.microsoft.com> wrote in message
news:596BE9A5-78A4-4B14-B338-E0207BA72F97@microsoft.com...
> I would like to change the background color if the value in the cell is
> between a certain numerical range to create a color map. I've got some
basic
> VB code, but I cannot figure out how to 'execute' the code to update all
the
> background colors based on values and formulas already populated in the
cell.
> If I manually type a value in the cell and hit return, the code seems to
> work.
>
> How do I 'execute' or 'run' the code using existing cell values/formulas?
> There isn't a "run" button to execute the code.
>
> Here's what I have currently using other peoples sample codes:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Range("D37:AA62")) Is Nothing Then
> 'If Not Intersect(Target, Range("E107:E118")) Is Nothing Then
> With Target
> Select Case Target.Value
> Case -13.8 To -11#
> Target.Interior.ColorIndex = 41
> Case -10.9 To -8.5
> Target.Interior.ColorIndex = 33
> Case -8.4 To -6#
> Target.Interior.ColorIndex = 37
> Case -5.9 To -3.5
> Target.Interior.ColorIndex = 42
> Case -3.4 To -1#
> Target.Interior.ColorIndex = 34
> Case -0.9 To 1.5
> Target.Interior.ColorIndex = 38
> Case 1.6 To 4#
> Target.Interior.ColorIndex = 4
> Case 4.1 To 6.5
> Target.Interior.ColorIndex = 35
> Case 6.6 To 9#
> Target.Interior.ColorIndex = 6
> Case 9.1 To 11.5
> Target.Interior.ColorIndex = 40
> Case 11.6 To 14#
> Target.Interior.ColorIndex = 45
> Case Is >= 14.1
> Target.Interior.ColorIndex = 3
>
> End Select
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
>
> End Sub
>

You could try this--it goes in a general module:

Option Explicit

Sub DoItOnce()
Dim Target As Range

On Error Resume Next

For Each Target In ActiveSheet.Range("d37:aa62").Cells
With Target
Select Case Target.Value
Case -13.8 To -11#
Target.Interior.ColorIndex = 41
Case -10.9 To -8.5
Target.Interior.ColorIndex = 33
Case -8.4 To -6#
Target.Interior.ColorIndex = 37
Case -5.9 To -3.5
Target.Interior.ColorIndex = 42
Case -3.4 To -1#
Target.Interior.ColorIndex = 34
Case -0.9 To 1.5
Target.Interior.ColorIndex = 38
Case 1.6 To 4#
Target.Interior.ColorIndex = 4
Case 4.1 To 6.5
Target.Interior.ColorIndex = 35
Case 6.6 To 9#
Target.Interior.ColorIndex = 6
Case 9.1 To 11.5
Target.Interior.ColorIndex = 40
Case 11.6 To 14#
Target.Interior.ColorIndex = 45
Case Is >= 14.1
Target.Interior.ColorIndex = 3

End Select
End With
Next Target
End Sub

kazoo wrote:
>
> I would like to change the background color if the value in the cell is
> between a certain numerical range to create a color map. I've got some basic
> VB code, but I cannot figure out how to 'execute' the code to update all the
> background colors based on values and formulas already populated in the cell.
> If I manually type a value in the cell and hit return, the code seems to
> work.
>
> How do I 'execute' or 'run' the code using existing cell values/formulas?
> There isn't a "run" button to execute the code.
>
> Here's what I have currently using other peoples sample codes:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Range("D37:AA62")) Is Nothing Then
> 'If Not Intersect(Target, Range("E107:E118")) Is Nothing Then
> With Target
> Select Case Target.Value
> Case -13.8 To -11#
> Target.Interior.ColorIndex = 41
> Case -10.9 To -8.5
> Target.Interior.ColorIndex = 33
> Case -8.4 To -6#
> Target.Interior.ColorIndex = 37
> Case -5.9 To -3.5
> Target.Interior.ColorIndex = 42
> Case -3.4 To -1#
> Target.Interior.ColorIndex = 34
> Case -0.9 To 1.5
> Target.Interior.ColorIndex = 38
> Case 1.6 To 4#
> Target.Interior.ColorIndex = 4
> Case 4.1 To 6.5
> Target.Interior.ColorIndex = 35
> Case 6.6 To 9#
> Target.Interior.ColorIndex = 6
> Case 9.1 To 11.5
> Target.Interior.ColorIndex = 40
> Case 11.6 To 14#
> Target.Interior.ColorIndex = 45
> Case Is >= 14.1
> Target.Interior.ColorIndex = 3
>
> End Select
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
>
> End Sub

--

Dave Peterson