Conditional Formatting using VBA

G

Guest

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
(e-mail address removed)
 
R

Ron Rosenfeld

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
Click to expand...

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
 
G

Guest

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?
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
G

Guest

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!!!!!!
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?
Click to expand...

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
Click to expand...
 
R

Ron Rosenfeld

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!!!!!!
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fill Color Macro 5
Watch not working for action on other cells 1
VB Cond. Formatting of Formula Cell 1
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count > 1"don't work 4
Interior color disappears 1
worksheet_change colour of a row on change of cell 15
Case Select 11
How can I modify my code to offset the defined range and repeat theprocedure instead of duplicating 4

Top