Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Continuous Execution
Just getting started with Visual Basic.
I have a sinple worksheet. Cell B1 contains the formula: "SUM(A1:A5). The Macro is: Sub ConditionalBkgChg() If Range("B1") < 5 Then Range("B1").Select Selection.Interior.ColorIndex = xlNone Range("A1").Select Else Range("B1").Select Selection.Interior.ColorIndex = 46 Range("A1").Select End If End Sub If B1 is 5 or greater then the background color turns red. The Macro works but... I have to run the macro manually after changing the values. How can I make the macro run continuously? Thanks Johnb3 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Continuous Execution
hi
first, you don't want the macro to run continuously. excel would be continuously locked up. secondly if all you are doing is change the background color as values on the sheet change, you would be better to use conditional formatting to achieve this. read up on conditional formating. in this case, select B5..... 2003 on the menu barformatconditional formatting cell value is.....<5.......pick your format 2007 home tabstyle groupetc Regards FSt1 "Johnb34" wrote: Just getting started with Visual Basic. I have a sinple worksheet. Cell B1 contains the formula: "SUM(A1:A5). The Macro is: Sub ConditionalBkgChg() If Range("B1") < 5 Then Range("B1").Select Selection.Interior.ColorIndex = xlNone Range("A1").Select Else Range("B1").Select Selection.Interior.ColorIndex = 46 Range("A1").Select End If End Sub If B1 is 5 or greater then the background color turns red. The Macro works but... I have to run the macro manually after changing the values. How can I make the macro run continuously? Thanks Johnb3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Continuous Execution
An Excellent question! You need an worksheet event macro:
Private Sub Worksheet_Calculate() Set b1 = Range("B1") v = b1.Value If v < 5 Then b1.Interior.ColorIndex = xlNone Else b1.Interior.ColorIndex = 46 End If End Sub NOTE: I avoided Selecting A1 Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200811 "Johnb34" wrote: Just getting started with Visual Basic. I have a sinple worksheet. Cell B1 contains the formula: "SUM(A1:A5). The Macro is: Sub ConditionalBkgChg() If Range("B1") < 5 Then Range("B1").Select Selection.Interior.ColorIndex = xlNone Range("A1").Select Else Range("B1").Select Selection.Interior.ColorIndex = 46 Range("A1").Select End If End Sub If B1 is 5 or greater then the background color turns red. The Macro works but... I have to run the macro manually after changing the values. How can I make the macro run continuously? Thanks Johnb3 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Continuous Execution
excue me. select the cell(s) you want to apply conditional formatting to.
regards FSt1 "FSt1" wrote: hi first, you don't want the macro to run continuously. excel would be continuously locked up. secondly if all you are doing is change the background color as values on the sheet change, you would be better to use conditional formatting to achieve this. read up on conditional formating. in this case, select B5..... 2003 on the menu barformatconditional formatting cell value is.....<5.......pick your format 2007 home tabstyle groupetc Regards FSt1 "Johnb34" wrote: Just getting started with Visual Basic. I have a sinple worksheet. Cell B1 contains the formula: "SUM(A1:A5). The Macro is: Sub ConditionalBkgChg() If Range("B1") < 5 Then Range("B1").Select Selection.Interior.ColorIndex = xlNone Range("A1").Select Else Range("B1").Select Selection.Interior.ColorIndex = 46 Range("A1").Select End If End Sub If B1 is 5 or greater then the background color turns red. The Macro works but... I have to run the macro manually after changing the values. How can I make the macro run continuously? Thanks Johnb3 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Continuous Execution
Hi John
Read Chip's page about events: http://www.cpearson.com/excel/Events.aspx But don't use a macro for this in real life, use conditional formating. HTH. Best wishes Harald "Johnb34" wrote in message ... Just getting started with Visual Basic. I have a sinple worksheet. Cell B1 contains the formula: "SUM(A1:A5). The Macro is: Sub ConditionalBkgChg() If Range("B1") < 5 Then Range("B1").Select Selection.Interior.ColorIndex = xlNone Range("A1").Select Else Range("B1").Select Selection.Interior.ColorIndex = 46 Range("A1").Select End If End Sub If B1 is 5 or greater then the background color turns red. The Macro works but... I have to run the macro manually after changing the values. How can I make the macro run continuously? Thanks Johnb3 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Continuous Execution
Thanks for your patience with "newbies" and for the suggestions
-- johnb3 "FSt1" wrote: hi first, you don't want the macro to run continuously. excel would be continuously locked up. secondly if all you are doing is change the background color as values on the sheet change, you would be better to use conditional formatting to achieve this. read up on conditional formating. in this case, select B5..... 2003 on the menu barformatconditional formatting cell value is.....<5.......pick your format 2007 home tabstyle groupetc Regards FSt1 "Johnb34" wrote: Just getting started with Visual Basic. I have a sinple worksheet. Cell B1 contains the formula: "SUM(A1:A5). The Macro is: Sub ConditionalBkgChg() If Range("B1") < 5 Then Range("B1").Select Selection.Interior.ColorIndex = xlNone Range("A1").Select Else Range("B1").Select Selection.Interior.ColorIndex = 46 Range("A1").Select End If End Sub If B1 is 5 or greater then the background color turns red. The Macro works but... I have to run the macro manually after changing the values. How can I make the macro run continuously? Thanks Johnb3 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Continuous Execution
OK. Thanks
-- johnb3 "FSt1" wrote: excue me. select the cell(s) you want to apply conditional formatting to. regards FSt1 "FSt1" wrote: hi first, you don't want the macro to run continuously. excel would be continuously locked up. secondly if all you are doing is change the background color as values on the sheet change, you would be better to use conditional formatting to achieve this. read up on conditional formating. in this case, select B5..... 2003 on the menu barformatconditional formatting cell value is.....<5.......pick your format 2007 home tabstyle groupetc Regards FSt1 "Johnb34" wrote: Just getting started with Visual Basic. I have a sinple worksheet. Cell B1 contains the formula: "SUM(A1:A5). The Macro is: Sub ConditionalBkgChg() If Range("B1") < 5 Then Range("B1").Select Selection.Interior.ColorIndex = xlNone Range("A1").Select Else Range("B1").Select Selection.Interior.ColorIndex = 46 Range("A1").Select End If End Sub If B1 is 5 or greater then the background color turns red. The Macro works but... I have to run the macro manually after changing the values. How can I make the macro run continuously? Thanks Johnb3 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Continuous Execution
Thanks for supporting "newbies." I really appreciate the links to learn the
details. -- johnb3 "Gary''s Student" wrote: An Excellent question! You need an worksheet event macro: Private Sub Worksheet_Calculate() Set b1 = Range("B1") v = b1.Value If v < 5 Then b1.Interior.ColorIndex = xlNone Else b1.Interior.ColorIndex = 46 End If End Sub NOTE: I avoided Selecting A1 Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200811 "Johnb34" wrote: Just getting started with Visual Basic. I have a sinple worksheet. Cell B1 contains the formula: "SUM(A1:A5). The Macro is: Sub ConditionalBkgChg() If Range("B1") < 5 Then Range("B1").Select Selection.Interior.ColorIndex = xlNone Range("A1").Select Else Range("B1").Select Selection.Interior.ColorIndex = 46 Range("A1").Select End If End Sub If B1 is 5 or greater then the background color turns red. The Macro works but... I have to run the macro manually after changing the values. How can I make the macro run continuously? Thanks Johnb3 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Continuous Execution
Thanks for the help and the link to more information.
-- johnb3 "Harald Staff" wrote: Hi John Read Chip's page about events: http://www.cpearson.com/excel/Events.aspx But don't use a macro for this in real life, use conditional formating. HTH. Best wishes Harald "Johnb34" wrote in message ... Just getting started with Visual Basic. I have a sinple worksheet. Cell B1 contains the formula: "SUM(A1:A5). The Macro is: Sub ConditionalBkgChg() If Range("B1") < 5 Then Range("B1").Select Selection.Interior.ColorIndex = xlNone Range("A1").Select Else Range("B1").Select Selection.Interior.ColorIndex = 46 Range("A1").Select End If End Sub If B1 is 5 or greater then the background color turns red. The Macro works but... I have to run the macro manually after changing the values. How can I make the macro run continuously? Thanks Johnb3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Execution | Excel Discussion (Misc queries) | |||
Halt Macro Execution | Excel Programming | |||
Continuous running macro | Excel Programming | |||
Macro Execution Time | Excel Programming | |||
slow macro execution | Excel Programming |