Delete cell contents based on another cell value

[BigTC]

Hello All,

Im pulling my hair out just slightly trying to find some code that removes data from cells G14 & H14 based on text criteria in cell P14. I have managed to figure out some code that works for 1 row only but require this to be a running theme all the way down the sheet.

Any help would be greatly appreciated.

Tony

 

[My Aswer Is This]

What is the criteria?
Show me a example

What might we see in P14

 

[BigTC]

What is the criteria?
Show me a example

What might we see in P14

Hi. The criteria in P14 will be the word 'Installed' This will then remove the data in cells g14 and h14

 

[My Aswer Is This]

So you want to search down Column P and any time we fine

Installed

We clear the data in column g and h of the same row

Is that what you want?
​

 

[My Aswer Is This]

If that is what you want then:
Try this:

Sub Clear_Me()
'Modified 2/20/2019 5:35:22 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "P").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, "P").Value = "Installed" Then
        Cells(i, "G").Resize(, 2).ClearContents
        End If
Next
Application.ScreenUpdating = True
End Sub

 

[BigTC]

So you want to search down Column P and any time we fine

Installed

We clear the data in column g and h of the same row

Is that what you want?
​

yes thats correct. as soon as the word installed is typed into cell p it will remove the data in column g and h on same row.

hope that helps.

 

[My Aswer Is This]

Well then you want a sheet change event script which runs automatically when you manually enter a value in column P


I assume you will be entering this value Manually.

Or do you expect this to happen as the result of a formula change.

See we need details like this.

 

[My Aswer Is This]

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
This script runs when you enter "Installed" in Column P Manually

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/20/2019  5:53:22 AM  EST
If Target.Column = 16 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "Installed" Then Target.Offset(, -9).Resize(, 2).ClearContents
End If
End Sub

 

[BigTC]

Well then you want a sheet change event script which runs automatically when you manually enter a value in column P


I assume you will be entering this value Manually.

Or do you expect this to happen as the result of a formula change.

See we need details like this.

im not sure myself if this can happen as a result from a formula or VBA. As soon as the word 'Installed' is inputed into cell p14 (whether it is manually typed in or used from dropdown list) i need it to erase the data is cells G and H on the same row.....im not sure i follow on what else i can explain what i dont actually know :/

Small screen snip if that shows anything of help?
https://ibb.co/hWD5P06

I appreciate your time looking into this.

 

[My Aswer Is This]

Well I gave you a answer that works but if do not think it will work without even trying then I don't know what to say.