Running macros from formula bar

[Guest]

I have a macro that changes the color in certain cells based on the result in
a particular cell. My question is this: can I have it run automatically
based on any new value in that cell or am I forced to run it with a toolbar.
I would prefer that it be done automatically. The basics are these: I have
an excel workbook that contains information about items for sale from
suppliers. One formula computes my minimum profit margin and displays both
the $ amount and the % amount. Another formula computes the maximum
percentage. I have done a macro that changes the backgound color of certain
cells based on the % obtained. Changes cells to red if below 0%. I did a
macro because I could not figure out how to do this with a formula. The
macro is as follows (the commented out portions do not work at present, but
the rest does):

Public Sub ChangeColor()
' Worksheets("Orders").Range("D18").Select
' With Selection
' MAXPPA = "MAXPMA / SMSRP"
If Worksheets("Orders").Range("D18").Value < 0 Then
Worksheets("Orders").Range("C17:C18").Interior.ColorIndex = 3
Else
Worksheets("Orders").Range("C17:C18").Interior.ColorIndex = 0
End If
' End With

End Sub

I would like this to run automatically based on the value in cell D18,
rather than having it run from a button.

Thanks,

/s/ Gene

 

[Dave Peterson]

You may be able to tie into some worksheet event (_change, _calculate), but have
you thought of using format|conditional formatting instead.

You can refer to cells in a different worksheet. If you name that range
(insert|name|define) that's used in the format|Conditional formatting dialog.

 

[Sebation.G]

u shd use worksheet event (worksheet_selectionchange)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if target=range("d18") then
'put your code here
end if
End Sub

 

[Guest]

Thank you Dave. I did not realize it was so easy

You may be able to tie into some worksheet event (_change, _calculate), but have
you thought of using format|conditional formatting instead.

You can refer to cells in a different worksheet. If you name that range
(insert|name|define) that's used in the format|Conditional formatting dialog.

 

[Guest]

Thank you Sebation. I will play with your suggestion later. The other
method got what I wanted in the near term. I am better at vbscript than excel
programming.
/s/ Gene