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.Col orIndex = 3
Else
Worksheets("Orders").Range("C17:C18").Interior.Col orIndex = 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

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.



Gene wrote:

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.Col orIndex = 3
Else
Worksheets("Orders").Range("C17:C18").Interior.Col orIndex = 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

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

--
Regards,

Sebation.G


"Gene" ...
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.Col orIndex = 3
Else
Worksheets("Orders").Range("C17:C18").Interior.Col orIndex = 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

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

"Dave Peterson" wrote:

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.



Gene wrote:

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.Col orIndex = 3
Else
Worksheets("Orders").Range("C17:C18").Interior.Col orIndex = 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

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

"Sebation.G" wrote:

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

--
Regards,

Sebation.G


"Gene" ...
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.Col orIndex = 3
Else
Worksheets("Orders").Range("C17:C18").Interior.Col orIndex = 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