Clear Cell Based on Another Cell Value

[hajiali]

Im trying to Clear a value in "A3:A120" in sheet named (CSA1) if the value of Cell L2 in Sheet named (RESULTS) = "YES". I found the following Solution that Will only clear the cells if the YES is in cell L2 in CSA1 Sheet and tired to use to to pull the YES from the RESULTS SHEET: The Below is what I have tired.

'Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("RESULTS!L2")) Is Nothing Then
If UCase(Range("RESULTS!L2")) = "YES" Then
Application.EnableEvents = False
Range("A3:A120").ClearContents
Application.EnableEvents = True
End If
End If
End Sub

Im tring to Avoid using a Macro bc it will be over 100+ macros. I just want a VBA code if any possible

 

[footoo]

Try this. Put it in the RESULTS sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [L2]) Is Nothing And UCase([L2]) = "YES" Then _
    Sheets("CSA1").Range("A3:A120").ClearContents
End Sub

Don't understand the relevance of this comment :

Im tring to Avoid using a Macro bc it will be over 100+ macros.

 

[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 Named RESULTS
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  1/23/2019  8:48:00 PM  EST
If Target.Address = "$L$2" Then
If Target.Value = UCase("Yes") Then Sheets("CSA1").Range("A3:A120").ClearContents
End If
End Sub

 

[hajiali]

Thank you so much Footoo. I have been searching for this solution was not able but this work out great.

How do I add "L3" = "YES" in RESULTS sheets to clear whats in CSA 2 tab so to Sheets("CSA2").Range("A3:A120").ClearContents?

 

[hajiali]

Never mind I figure it out thank you so much

 

[footoo]

.