If specific cell is changed and contains specific phrase then change other cells

[tomleitch]

Hi,

I am trying to make a worksheet change other cells to "N/A" if cells contain a specific text.

For example if any cell in range J10:J5010 is changed then cells offset to the right "S, T and V" columns of that particular row are changed to N/A.

I was thinking I could do this on the Worksheet_Change(ByVal Target As Range) code that I have??


Thanks
Tom

 

[My Aswer Is This]

I have the below script working like you wanted if you make any change to column J below row 10

Now you said:

I am trying to make a worksheet change other cells to "N/A" if cells contain a ​

specific​

text.

But then you said:
For example if any cell in range J10:J5010 is changed​

.

So if the cell must have a specific Text then I need to know what specific text.

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/24/2018  4:40:01 AM  EDT
If Target.Column = 10 And Target.Row > 9 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
ans = Target.Row
Application.Union(Cells(ans, 19), Cells(ans, 20), Cells(ans, 22)).Value = "NA"
End If
End Sub

 

[tomleitch]

That's perfect... added a couple of bits and it's just what I needed.

Many thanks
Tom

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

That's perfect... added a couple of bits and it's just what I needed.

Many thanks
Tom