Macro Continuous Execution

[Johnb34]

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

 

[FSt1]

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 bar>format>conditional formatting
cell value is.....<5.......pick your format
2007 home tab>style group>etc

Regards
FSt1

 

[Gary''s Student]

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

 

[FSt1]

excue me. select the cell(s) you want to apply conditional formatting to.

regards
FSt1

 

[Harald Staff]

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

 

[johnb3]

Thanks for your patience with "newbies" and for the suggestions

 

[johnb3]

OK. Thanks
--
johnb3

excue me. select the cell(s) you want to apply conditional formatting to.

regards
FSt1

 

[johnb3]

Thanks for supporting "newbies." I really appreciate the links to learn the
details.

 

[johnb3]

Thanks for the help and the link to more information.