Conditional Formatting using VBA

Hello,
I have a number in cell B1 that is generated from a simple sum formula. I
want the background color in cell B4 to change depending on what value is
displayed in B1. I know how to do that using CF or by use of the Select
Case.Value code (See sample code below). 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

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: Range("B4").Interior.ColorIndex = 2
Case 1: Range("B4").Interior.ColorIndex = 10
Case 2: Range("B4").Interior.ColorIndex = 10
Case 3: Range("B4").Interior.ColorIndex = 10
Case 4: Range("B4").Interior.ColorIndex = 10
Case 5: Range("B4").Interior.ColorIndex = 10
Case 6: Range("B4").Interior.ColorIndex = 10
Case 7: Range("B4").Interior.ColorIndex = 6
Case 8: Range("B4").Interior.ColorIndex = 6
Case 9: Range("B4").Interior.ColorIndex = 6
Case 10: Range("B4").Interior.ColorIndex = 6
Case 11: Range("B4").Interior.ColorIndex = 6
Case 12: Range("B4").Interior.ColorIndex = 6
Case 13: Range("B4").Interior.ColorIndex = 3
Case 14: Range("B4").Interior.ColorIndex = 3
Case 15: Range("B4").Interior.ColorIndex = 3
Case 16: Range("B4").Interior.ColorIndex = 3
Case 17: Range("B4").Interior.ColorIndex = 3
Case 18: Range("B4").Interior.ColorIndex = 3
Case 19: Range("B4").Interior.ColorIndex = 3
Case 20: Range("B4").Interior.ColorIndex = 3
Case 21: Range("B4").Interior.ColorIndex = 3
Case 22: Range("B4").Interior.ColorIndex = 3
Case Is > 22: Range("B4").Interior.ColorIndex = 3
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

P.S.
I borrowed the initial Case Is code from someone on here but cant remember
who it came from. But Thanks for the bones!

JW
[email protected]

On Wed, 2 Mar 2005 18:01:04 -0800, JWCardington
<[email protected]> 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

Ron,
Thank you very much. This works great if all I am doing is typing the
numbers into cell b1. But the number that will populate b1 is the result of
a "sum" formula that totals the number in another range, say c1:d1 for
example. This doesnt work if I put my formula in cell b1.

What else do I need?

"Ron Rosenfeld" wrote:

> On Wed, 2 Mar 2005 18:01:04 -0800, JWCardington
> <[email protected]> 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
>

On Thu, 3 Mar 2005 03:43:03 -0800, JWCardington <[email protected]>
wrote:

>Ron,
>Thank you very much. This works great if all I am doing is typing the
>numbers into cell b1. But the number that will populate b1 is the result of
>a "sum" formula that totals the number in another range, say c1:d1 for
>example. This doesnt work if I put my formula in cell b1.
>
>What else do I need?

What does "doesn't work" mean?

The way you have written the VBA routine, it will only "do something" if and
only if Target is B1. But Target represents the changed range but not if the
change is the result of a calculation.

Since B1 contains a formula that changes as the result of entries in C1:D1,
your Sub your first "IF" statement will evaluate as FALSE, and the Sub will
exit.

2 possible solutions:

1. Forget the IF statement. Evaluate B1 after every Change (or calculate)
event.

2. Change the IF statement to look at the Precedents of B1:

================================
....
If Not Intersect(Target, Range("C1:D1")) Is Nothing Then
With [B1]
Select Case .Value
....
===============================


--ron

Ron,
Thank you very much for your last responce. It was exactly what I needed to
get me on the right track. I did have to hange my IF statement to look at
the precedents of B1:. At first I was having difficulty with it until I had
to include ALL precedent ranges that were invloved to create the final value
in B1. Below are the ranges I had to set in my If statement.

(I15:J24,N15:N24,O15:O25,H36:H140).

Once I did that it worked perfectly!

Again,
Thank you!!!!!!

> On Thu, 3 Mar 2005 03:43:03 -0800, JWCardington <[email protected]>
> wrote:
>
> >Ron,
> >Thank you very much. This works great if all I am doing is typing the
> >numbers into cell b1. But the number that will populate b1 is the result of
> >a "sum" formula that totals the number in another range, say c1:d1 for
> >example. This doesnt work if I put my formula in cell b1.
> >
> >What else do I need?
>
> What does "doesn't work" mean?
>
> The way you have written the VBA routine, it will only "do something" if and
> only if Target is B1. But Target represents the changed range but not if the
> change is the result of a calculation.
>
> Since B1 contains a formula that changes as the result of entries in C1:D1,
> your Sub your first "IF" statement will evaluate as FALSE, and the Sub will
> exit.
>
> 2 possible solutions:
>
> 1. Forget the IF statement. Evaluate B1 after every Change (or calculate)
> event.
>
> 2. Change the IF statement to look at the Precedents of B1:
>
> ================================
> ....
> If Not Intersect(Target, Range("C1:D1")) Is Nothing Then
> With [B1]
> Select Case .Value
> ....
> ===============================
>
>
> --ron
>

On Thu, 3 Mar 2005 18:41:01 -0800, JWCardington <[email protected]>
wrote:

>Ron,
>Thank you very much for your last responce. It was exactly what I needed to
>get me on the right track. I did have to hange my IF statement to look at
>the precedents of B1:. At first I was having difficulty with it until I had
>to include ALL precedent ranges that were invloved to create the final value
>in B1. Below are the ranges I had to set in my If statement.
>
>(I15:J24,N15:N24,O15:O25,H36:H140).
>
>Once I did that it worked perfectly!
>
>Again,
>Thank you!!!!!!

You're welcome. Glad you got things working OK.
--ron