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

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

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

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

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

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

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

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

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