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

Multiple VB Conditional Formats for Numerical Ranges
 

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" wrote in message
...
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

Multiple VB Conditional Formats for Numerical Ranges
 

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