Clear the content of a cell using VBA

  • Thread starter Thread starter Yee
  • Start date Start date
Y

Yee

I am trying to write a mini VB marco
that clears the entire content of a cell
upon a certain condition from other cell.
Pls help on the coding.
 
with activesheet
if .range("a1").value > 5 then
.range("d1").clearcontents
end if
end with

But it sounds like you may want to run it automatically:

Try this in a test worksheet.
rightclick on the sheet tab and select view code.
paste this in the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
If .Value > 5 Then
Application.EnableEvents = False
.Offset(0, 3).ClearContents
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Then back to excel and put some test date in A1:D20

Then change some stuff in column A--put numbers bigger than 5.

The code looks for changes in column A. If it finds a change and the new value
is >5, it clears the cell 3 columns to the right (column D).
 
Back
Top