| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy one...
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one...
Use conditional formating on the worksheet. Change setting to Cell Value Less than and put I5 in the value box. "John" wrote: 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one...
J5: =IF(V5<I5,"STOP","") and set conditional formatting when J5 value ="STOP" "John" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one...
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 "Patrick Molloy" wrote: J5: =IF(V5<I5,"STOP","") and set conditional formatting when J5 value ="STOP" "John" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one...
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 "John" wrote in message ... 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 "Patrick Molloy" wrote: J5: =IF(V5<I5,"STOP","") and set conditional formatting when J5 value ="STOP" "John" wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one...
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? "Patrick Molloy" wrote: 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 "John" wrote in message ... 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 "Patrick Molloy" wrote: J5: =IF(V5<I5,"STOP","") and set conditional formatting when J5 value ="STOP" "John" wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one...
the sheet's code page - so right click the tab and select View Code from the pop-up "John" wrote in message ... 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? "Patrick Molloy" wrote: 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 "John" wrote in message ... 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 "Patrick Molloy" wrote: J5: =IF(V5<I5,"STOP","") and set conditional formatting when J5 value ="STOP" "John" wrote in message ... 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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one...
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. -- Rick (MVP - Excel) "John" wrote in message ... 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? "Patrick Molloy" wrote: 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 "John" wrote in message ... 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 "Patrick Molloy" wrote: J5: =IF(V5<I5,"STOP","") and set conditional formatting when J5 value ="STOP" "John" wrote in message ... 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? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one...
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 "John" wrote: 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 "Patrick Molloy" wrote: J5: =IF(V5<I5,"STOP","") and set conditional formatting when J5 value ="STOP" "John" wrote in message ... 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? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one...
....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. "EricG" wrote: 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 "John" wrote: 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 "Patrick Molloy" wrote: J5: =IF(V5<I5,"STOP","") and set conditional formatting when J5 value ="STOP" "John" wrote in message ... 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? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Easy one...
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 "EricG" wrote: ...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. "EricG" wrote: 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 "John" wrote: 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 "Patrick Molloy" wrote: J5: =IF(V5<I5,"STOP","") and set conditional formatting when J5 value ="STOP" "John" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Easy one..not for me.. | Excel Discussion (Misc queries) | |||
| Should be easy | Excel Discussion (Misc queries) | |||
| How to sum in an easy way? | Excel Discussion (Misc queries) | |||
| Probably easy but I need help! | Excel Worksheet Functions | |||
| new user with easy question? not easy for me | New Users to Excel | |||
Hybrid Mode
