Clear contents of one cell in a range based on the value of another cell in a range using VBA

[ryankendrick]

I want to clear the value in a cell once the criteria has been met in another cell. Both have cell ranges and I was trying to use this code, but I can't get anything to work. I have one range (C3:C60) and once the task is marked as COMPLETE for each cell in that range (E3:E60), I want the contents of the first cell to be cleared. I only want the cell on the same row as the "COMPLETE" to be cleared, not the entire range. Any assistance would be greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("E3:E60") Then
If Target.Value = UCase("COMPLETE") Then Sheets("SHEET1").Range("C3:C60").ClearContents
End If
End Sub

EXAMPLE-----

Electronic Boost Controller	eBay	$ 45.88	2​
Turbo Oiling System	eBay	$ 48.98 (CLEAR)	2​	COMPLETE (INPUT)
Recirculating oil catch can	Amazon	$ 33.99	2​
Performance fuel injectors 42 LBS	eBay	$ 34.49	2​
Summit Racing High Output Ignition Coils	Summit Racing	$ 261.99	2​	COMPLETE
Summit Racing plug wires	Summit Racing	$ 53.00	2​	COMPLETE
NGK Sprak Plugs	Amazon	$ 58.00	2​	COMPLETE
MSD LS coil power upgrade harness	Amazon	$ 85.23	2​
Remapping ECU	???	$ 1,400.00	3​
Replacement radiator with electric fans	Amazon	$ 219.99	2​
3" muffler Stainless 2 Chamber	Summit Racing	$ 49.99	4​
3" turn down	Amazon	$ 27.99	4​
Box frame			4​
Fender flares	Amazon	$ 363.79	4​
Sand truck - 300 grit			4​
Epoxy Primer	Summit Racing	$ 51.99	4​
Epoxy Catalyst	Summit Racing	$ 47.99	4​
Paint truck - jet black	Summit Racing	$ 89.99	4​
Door trim	Amazon	$ 111.00	4​
Rear whell well liners	Amazon	$ 99.95	4​

 

[mumps]

Place this macro in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("E3:E60")) Is Nothing Then Exit Sub
    If Target = "COMPLETE" Then
        Range("A" & Target.Row).ClearContents
    End If
End Sub

 

[ryankendrick]

Place this macro in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("E3:E60")) Is Nothing Then Exit Sub
    If Target = "COMPLETE" Then
        Range("A" & Target.Row).ClearContents
    End If
End Sub

PERFECT. Thank you so much. I was pulling my hair out. LOL

 

[mumps]

You are very welcome.