Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting using VBA
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting using VBA
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting using VBA
On Thu, 3 Mar 2005 03:43:03 -0800, JWCardington
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting using VBA
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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting using VBA
On Thu, 3 Mar 2005 18:41:01 -0800, JWCardington
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |