Clear cell content

[adam]

Hi anyone,

How could I write a worksheet event code that would clear the content of column C when I clear the content of column F from any row.

Any help on this would be kindly appreciated.

Thanks in advance.

[HansV]

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
  If Not Intersect(Range("F:F"), Target) Is Nothing Then
    Application.EnableEvents = False
    For Each rng In Intersect(Range("F:F"), Target)
      If rng.Value = "" Then
        rng.Offset(0, -3).ClearContents
      End If
    Next rng
    Application.EnableEvents = True
  End If
End Sub

[adam]

Thanks for the help Hans. The code clears column C from the same sheet. How could the code furthermore be made to clear the value in column C from sheet 2 when the cell from column F of sheet 1 one is cleared.

[HansV]

Change

rng.Offset(0, -3).ClearContents

to

Worksheets("Sheet2").Range("C" & rng.Row).ClearContents

[adam]

Thanks for the help Hans.