VBA Code to Enter a String Text when the trigger cell's value meets certain conditions

[JohnGow383]

Hi all,

I would like code to automatically generate text in a cell when another cell's value meets the preset conditions. I may also want to add a message box too.
So for example:
Trigger Cell = H6 if Value of H6<0 or H6>200 to populate merged cells "H13:K14" with text "Warning! Please Check the LSFO Meter Readings"
To also add a message box to the same effect (I can remove later if this is too annoying).
Once the error condition is no longer met, i.e. the value of H6 is between 0 to 200, for the populated merged cell "H13:K14" to have it's contents cleared.

I also have other trigger cells, where the warning message would be slightly different, but would it be just a case of repeating a small section of the code within the same macro or would I have to make multiple macros for each trigger cell?

Thanks

 

[bebo021999]

Try:

1) Its good practise to put:
"Option Explicit" on top of code
try it.

2) to replace:
"If Not Application.Intersect(triggercells, Range(Target.Address)) Is Nothing Then"
with
"If Not Intersect(target, triggercells) Is Nothing Then"

to see if it in good order?

 

[JohnGow383]

I'm just thinking, is it not working because the H6 contains a formula? The value of H6 though is a numerical number so it shouldn't really matter.

 

[JohnGow383]

will try now thanks

Try:

1) Its good practise to put:
"Option Explicit" on top of code
try it.

2) to replace:
"If Not Application.Intersect(triggercells, Range(Target.Address)) Is Nothing Then"
with
"If Not Intersect(target, triggercells) Is Nothing Then"

to see if it in good order?

 

[bebo021999]

Or, try to delete first part (your initial code), just use only second (my code), then try to change value in H6

???

 

[bebo021999]

I'm just thinking, is it not working because the H6 contains a formula? The value of H6 though is a numerical number so it shouldn't really matter.

That why H6 was not triggerred.
Change even only work for manual input.
For change from formula, it should be fired with worksheet_calculate even. (calculate does not fire single range, it fire change from every cells in whole sheet)

 

[JohnGow383]

No, that hasn't helped. The
"If Not Intersect(target, triggercells) Is Nothing Then" line still works with the other parts of the code though.
It is not being triggered for some reason

Try:

1) Its good practise to put:
"Option Explicit" on top of code
try it.

2) to replace:
"If Not Application.Intersect(triggercells, Range(Target.Address)) Is Nothing Then"
with
"If Not Intersect(target, triggercells) Is Nothing Then"

to see if it in good order?

 

[JohnGow383]

Sorry, bit confused

That why H6 was not triggerred.
Change even only work for manual input.
For change from formula, it should be fired with worksheet_calculate even. (calculate does not fire single range, it fire change from every cells in whole sheet)

Like something like this

Private Sub Worksheet_Calculate()
Dim ce1 As Range, msg1 As String
Set ce1 = Range("H6"): msg1 = "Warning! Please Check the LSFO Meter Readings"
    With Range("H13:K14")
        If Not Intersect(Target, ce1) Is Nothing Then
            Select Case ce1.Value
                Case 0 To 200
                    .ClearContents
                Case Else
                    .Value = msg1
                    MsgBox msg1
            End Select
        End If
    End With
End Sub

 

[bebo021999]

Does it work via calculate even?

 

[JohnGow383]

I'm getting an error message

Does it work via calculate even?

 

[bebo021999]

Opp, sorry, I forgot to tell you that calculate even does not require target. Try to remove 2 lines of "If ...End if"