Clearing cell contents based on another column's values

[The Tamer]

Hi

I need code to do this:

Look at F4:F79, find and all zeros, leaving blank cells in their place. And where a zero is removed in Column F, the text in the corresponding cell in column E should then be cleared too.

Any suggestions please?

Thanks a million.

 

[Glaswegian]

Hi Tamer

Would this do?

Sub ClearText()
Dim myRng As Range
Dim cell As Range

Set myRng = Range("F4:F79")

For Each cell In myRng
    If cell.Value = "0" Then
        cell.ClearContents
        cell.Offset(0, -1).ClearContents
    End If
Next cell
End Sub

HTH

Regards

 

[jindon]

Hi,

Right click on the sheet tab that you want to run this code then clilck
View Code, then paste the code below

Public z As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If z <> 0 Then Exit Sub
     If Intersect(Target, Me.Range("f4:f79")) Is Nothing Then Exit Sub
        Application.EnableEvents = False
            If IsEmpty(Target) Then
                Target.Offset(, -1).ClearContents
            End If
        Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Me.Range("f4:f79")) Is Nothing Then Exit Sub
        If Target.Count <> 1 Then Exit Sub
            z = Target.Value
End Sub

hope this helps

jindon

 

[The Tamer]

Hey both, thanks.

I always feel awful when two or more people have provided different answers and I can employ only one!

Both work well, and so you've both contributed to solving my problem.

So thanks

(but i used Glaswegian's)