Certain string in cell A1 causes a change in Cell B1
 

Dear Experts:

I would like to run a macro with the following requirements.

As soon as the cell value in A1 changes and the string "Mont" is part
of the cell value in A1, the Contents of B1 is to change to "This is
the responsibility of...). If this not the case nothing is to be
displayed in Cell B1

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Certain string in cell A1 causes a change in Cell B1
 

Hi
Would a formula do ?, if yes try this..
=IF(ISERROR(SEARCH("Mont",A1,1)),"","This is
the responsibility of..")
HTH
John
"andreashermle" wrote in message
...
Dear Experts:

I would like to run a macro with the following requirements.

As soon as the cell value in A1 changes and the string "Mont" is part
of the cell value in A1, the Contents of B1 is to change to "This is
the responsibility of...). If this not the case nothing is to be
displayed in Cell B1

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Certain string in cell A1 causes a change in Cell B1
 

This code is to be pasted into the sheet code module for the sheet with the
applicable cells a1 and b1. Right click the sheet name tab and select View
Code from the pop up menu. The appropriate code window should open.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then
If InStr(1, LCase(Target.Value), "mont") 0 Then
Range("B1") = "This is the responsibility of..."
Else
Range("B1") = ""
End If
End If
End Sub



"andreashermle" wrote in message
...
Dear Experts:

I would like to run a macro with the following requirements.

As soon as the cell value in A1 changes and the string "Mont" is part
of the cell value in A1, the Contents of B1 is to change to "This is
the responsibility of...). If this not the case nothing is to be
displayed in Cell B1

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Certain string in cell A1 causes a change in Cell B1
 

Hi,

may be this

Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(1, Range("A1"), "Mont", vbTextCompare) 0 Then
Application.EnableEvents = False
Range("B1").Value = "This is the responsibility of...)"
Else
Range("B1").ClearContents
End If
Application.EnableEvents = True
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"andreashermle" wrote:

Dear Experts:

I would like to run a macro with the following requirements.

As soon as the cell value in A1 changes and the string "Mont" is part
of the cell value in A1, the Contents of B1 is to change to "This is
the responsibility of...). If this not the case nothing is to be
displayed in Cell B1

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

.

Certain string in cell A1 causes a change in Cell B1
 

On 25 Feb., 15:53, "John" wrote:
Hi
Would a formula do ?, if yes try this..
=IF(ISERROR(SEARCH("Mont",A1,1)),"","This is
the responsibility of..")
HTH
John"andreashermle" wrote in message

...



Dear Experts:

I would like to run a macro with the following requirements.

As soon as the cell value in A1 changes and the string "Mont" is part
of the cell value in A1, the Contents of B1 is to change to "This is
the responsibility of...). If this not the case nothing is to be
displayed in Cell B1

Help is much appreciated. Thank you very much in advance.

Regards, Andreas- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi John,

great. It is working as desired. Thank you very much for your terrific
help.
Regards, Andreas

Certain string in cell A1 causes a change in Cell B1
 

Hi Andreashermle
You're welcome, anytime..
Regards
John
"andreashermle" wrote in message
...
On 25 Feb., 15:53, "John" wrote:
Hi
Would a formula do ?, if yes try this..
=IF(ISERROR(SEARCH("Mont",A1,1)),"","This is
the responsibility of..")
HTH
John"andreashermle" wrote in message

...



Dear Experts:

I would like to run a macro with the following requirements.

As soon as the cell value in A1 changes and the string "Mont" is part
of the cell value in A1, the Contents of B1 is to change to "This is
the responsibility of...). If this not the case nothing is to be
displayed in Cell B1

Help is much appreciated. Thank you very much in advance.

Regards, Andreas- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi John,

great. It is working as desired. Thank you very much for your terrific
help.
Regards, Andreas

Certain string in cell A1 causes a change in Cell B1
 

On 25 Feb., 16:03, "JLGWhiz" wrote:
This code is to be pasted into the sheet code module for the sheet with the
applicable cells a1 and b1. *Right click the sheet name tab and select View
Code from the pop up menu. *The appropriate code window should open.

Private Sub Worksheet_Change(ByVal Target As Range)
* If Target = Range("A1") Then
* * If InStr(1, LCase(Target.Value), "mont") 0 Then
* * * *Range("B1") = "This is the responsibility of..."
* * Else
* * * *Range("B1") = ""
* * End If
* End If
End Sub

"andreashermle" wrote in message

...



Dear Experts:

I would like to run a macro with the following requirements.

As soon as the cell value in A1 changes and the string "Mont" is part
of the cell value in A1, the Contents of B1 is to change to "This is
the responsibility of...). If this not the case nothing is to be
displayed in Cell B1

Help is much appreciated. Thank you very much in advance.

Regards, Andreas- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi JLG,

great / terrfic help. It works just fine. Regards, Andreas

Certain string in cell A1 causes a change in Cell B1
 

On 25 Feb., 16:28, Mike H wrote:
Hi,

may be this

Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(1, Range("A1"), "Mont", vbTextCompare) 0 Then
Application.EnableEvents = False
* * Range("B1").Value = "This is the responsibility of...)"
Else
* * Range("B1").ClearContents
End If
Application.EnableEvents = True
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.



"andreashermle" wrote:
Dear Experts:

I would like to run a macro with the following requirements.

As soon as the cell value in A1 changes and the string "Mont" is part
of the cell value in A1, the Contents of B1 is to change to "This is
the responsibility of...). If this not the case nothing is to be
displayed in Cell B1

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

.- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Mike.

Great support. Thank you very much. It works as desired. Regards,
Andreas