Easy one...

  • Thread starter Thread starter John
  • Start date Start date
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?
 
Use conditional formating on the worksheet. Change setting to Cell Value
Less than and put I5 in the value box.
 
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
 
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
 
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
 
....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.
 
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
 
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?
 
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.
 
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.
 
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)
 
Back
Top