Adapt code to make dynamic

[ddub25]

Sub GState()
If Range("F81") + Range("F82") > 0 Then
Range("H93").Value = "IP"
Else
Range("H93").Value = "GE"
End If
End Sub

I want to make this code dynamic so that the moment when the result changes from "IP" to "GE" is recognised and "END" is outputted into "H94". The code would need to be re-checking for the change in state continuously every second or so.

Can anyone help with adapting this code?

 

[JoeMo]

Sub GState()
If Range("F81") + Range("F82") > 0 Then
Range("H93").Value = "IP"
Else
Range("H93").Value = "GE"
End If
End Sub

I want to make this code dynamic so that the moment when the result changes from "IP" to "GE" is recognised and "END" is outputted into "H94". The code would need to be re-checking for the change in state continuously every second or so.

Can anyone help with adapting this code?

Are cells F81 and F82 manual entries or do they have formulas in them? What do you want in H94 if "GE" changes to "IP"?

 

[ddub25]

"Are cells F81 and F82 manual entries or do they have formulas in them?"
F81 and F82 will not be manual entries, an external source will be inputting values automatically (will not have formulas in them), although at the moment, while I am testing the code I am manually entering in these cells.

"What do you want in H94 if "GE" changes to "IP"?"
It can be left blank when this happens

 

[JoeMo]

"Are cells F81 and F82 manual entries or do they have formulas in them?"
F81 and F82 will not be manual entries, an external source will be inputting values automatically (will not have formulas in them), although at the moment, while I am testing the code I am manually entering in these cells.

"What do you want in H94 if "GE" changes to "IP"?"
It can be left blank when this happens

This is sheet code - install it following the steps below. Once installed it will react automatically to manual changes in either cell F81 or F82. Don't know if it will work with your external source since you didn't indicate what that source is.

To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F81")) Is Nothing Or Not Intersect(Target, Range("F82")) Is Nothing Then
    Application.EnableEvents = False
    If Range("F81") + Range("F82") > 0 Then
        Range("H93").Value = "IP"
        Range("H94") = ""
    Else
        Range("H93").Value = "GE"
        Range("H94").Value = "END"
    End If
    Application.EnableEvents = True
End If
End Sub

 

[ddub25]

Thanks very much, Joe, that's a great help. Works great and thanks for the thorough explanation, Dan

 

[JoeMo]

Thanks very much, Joe, that's a great help. Works great and thanks for the thorough explanation, Dan

You are welcome - thanks for the reply.