Change Cell contents

[Guest]

Hi All,

Is it possible to change the cell value when there is any word exist in that
cell?
e.g.

GPS; Ignition off
TM; Ignition off
ST; Ignition off
Ignition off

when there is Ignition off with anyother word the cell value change to
Ignition off.

Thanks & Regards

Hassan

 

[Guest]

Yes, with a change event macro. This is for cell B9, but can be modified for
any range:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("B9"), Target) Is Nothing Then Exit Sub
s = "Ignition off"
v = Target.Value
If InStr(1, v, s) = 0 Then Exit Sub
Application.EnableEvents = False
Target.Value = s
Application.EnableEvents = True
End Sub

Paste this in the worksheet code area, NOT a standard module.

 

[Guest]

Hi Gary,

Is it possible to change in active sheet?

Please write in standard module.

 

[Guest]

To make it work on the active sheet, the macro would have to be installed on
every sheet that the action is required. It is o.k. to make multiple copies
of the code, one for each wroksheet.

I see that it would be good to use a standard module but I don't know how to
do it.

 

[Dave Peterson]

Another option would be to make it into a workbook_sheetchange event.

This code is placed under the ThisWorkbook module (and the other worksheet
events should be removed).

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim s As String
Dim v As Variant

If Intersect(Sh.Range("B9"), Target) Is Nothing Then Exit Sub

s = "Ignition off"
v = Target.Value
If InStr(1, v, s, vbTextCompare) = 0 Then Exit Sub
Application.EnableEvents = False
Target.Value = s
Application.EnableEvents = True
End Sub

If there are sheets that should be ignored, then that could be added to the
code.