On Wed, 2 Mar 2005 18:01:04 -0800, JWCardington
wrote:

Now what I want to do is also
display a TEXT string in cell B4 that also changes based on the value
displayed in cell B1. The color of the font in this text string will also
need to change based on the value displayed in cell B1.

Also I need help condensing this code a bit by using number ranges instead
of each possible number that may be displayed in cell b1

I'm not quite sure what kind of text string you wish to display.

If it is going to repeat the entry in B1, then do a Range("B4").Value = "Value
in B1 is " & Range("B1").text or something like that before the Select Case.

If something different, see below where I've condensed your routine, as you
requested, and also added some statements to adjust font color and cell
contents:

===========================
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B1")) Is Nothing Then
With Target
Select Case .Value
Case Is < 1
With Range("B4")
.Interior.ColorIndex = 2
.Font.ColorIndex = xlAutomatic
.Value = "less than one"
End With

Case 1 To 6
With Range("B4")
.Interior.ColorIndex = 10
.Font.ColorIndex = xlAutomatic
.Value = "one to six"
End With
Case 7 To 12
With Range("B4")
.Interior.ColorIndex = 6
.Font.ColorIndex = xlAutomatic
.Value = "seven to twelve"
End With
Case Is 12
With Range("B4")
.Interior.ColorIndex = 3
.Font.ColorIndex = xlAutomatic
.Value = "greater than twelve"
End With

End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
================================


--ron