Easy one...

J

John

I have a sheet that updates from an external feed...

I want to change cell J5 to "STOP" and format it RED if cell V5 is below the
value found in I5

is there an 'on calculate' command?
 
J

Joel

Use conditional formating on the worksheet. Change setting to Cell Value
Less than and put I5 in the value box.
 
P

Patrick Molloy

J5: =IF(V5<I5,"STOP","")

and set conditional formatting when J5 value ="STOP"
 
J

John

I don't want the cell to change back if the value goes back above the set
value in I5... I want it to trigger and stay "STOP" so I don't want to use a
formula
 
P

Patrick Molloy

assuming V5 gets data after I5, maybe you could use the worksheet's change
event...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("V5").Address Then
If Range("V5") < Range("I5") Then
Range("J5").Value = "STOP"
Range("J5").Interior.Color = vbRed
End If
End If
End Sub

or just add the if check to whatever code you're running to pull in the data
 
E

EricG

Maybe a modified formula, again with conditional formatting:

=IF(J5="STOP","STOP",IF(V5<I5,"STOP",""))

This way, if J5 is already "STOP", it will stay that way. If it starts out
as blank, then it will only go to "STOP" when V5 becomes less than I5.

HTH,

Eric
 
E

EricG

....although I think I just created somewhat of a circular reference. The
only way to reset that cell's (J5) value after "STOP" is triggered is to
either modify the formula or to have a "Reset" button that resets the value
to blank.
 
E

EricG

Something like this:

Private Sub CommandButton1_Click()
ActiveSheet.Cells(5, 10).Value = ""
ActiveSheet.Cells(5, 10).Formula =
"=IF(J5=""STOP"",""STOP"",IF(V5<I5,""STOP"",""""))"
End Sub
 
J

John

I can't get it to run no matter what values are in my cells... code looks
perfect to me... where should I paste it?
 
P

Patrick Molloy

the sheet's code page - so right click the tab and select View Code from the
pop-up
 
R

Rick Rothstein

Right click the tab at the bottom of the worksheet that you want to have
this functionality, select View Code from the popup menu that appears and
copy/paste the code into the code window that opened up.
 
J

John

=IF(J5="STOP","STOP",IF(V5<I5,"STOP",""))

and allow circular references works for me... thanks
 
J

John

I cannot get the code to fire period... I tried typing STOP in J5 and running
this
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("V5").Address Then
If Range("V5") = "STOP" Then

Range("J5").Interior.Color = vbRed
End If
End If
End Sub

I am using the formula with circular references for now.
 
P

Patrick Molloy

using my code...
enter a value in any cell will fire the event
put a value like 10 in I5
now put a lower value in V5
when the code triggers, it checks if the cell changed was V5
if it was, then it compares the value in V5 to I5
if the value in V5 is less than the value in I5, the word STOP is placed in
cell J5 and cell J5 is colored red

mail me directly and i'll send you the example workbook (excel 2003 or 2007)
 
Top