Clearing cell contents on CellUpdate

Hello all!

I want to do the following and I think VBA is the way to do it: when I change the content of a cell (let's say restricted ones: the cells in columns B or C), I want 3 cells to the right of the one updated to become empty.

I'm very very new to VBA so any code part would be appreciated.

Thanks in advance,
Chris

First you need to know how to put the code into an event procedure (so it
runs when a particular event happens - in this case when a cell value is
changed). In the VBA editor show the Project Explorer (View menu, if not
showing already) and double-click on the sheet you want to do this on. Then,
at the top of the code pane note the two dropdown lists. Choose "Worksheet"
in the left one: the default event for the Worksheet is Selection Change so
the code pane will show a "blank" Worksheet_SelectionChange sub - you can
ignore this or erase it, doesn't matter. In the right dropdown box choose
"Change" and you should see:
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

This is the sub that will run when any cell on the sheet is changes - Target
will be the cell (or cells, in the event of a range of data being pasted or
cleared, etc.)

So this is the sub:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangedCell as Range

For Each ChangedCell in Target.Cells ' Loop through all cells that were
changed
With ChangedCell
' Check for column B or C and if so clear cell 3 columns to the right:
If .Column = 2 or .Column = 3 Then .Offset(0,3).ClearContents
End With
Next ChangedCell

End Sub

--
- K Dales


"loopoo" wrote:

>
> Hello all!
>
> I want to do the following and I think VBA is the way to do it: when I
> change the content of a cell (let's say restricted ones: the cells in
> columns B or C), I want 3 cells to the right of the one updated to
> become empty.
>
> I'm very very new to VBA so any code part would be appreciated.
>
> Thanks in advance,
> Chris
>
>
> --
> loopoo
> ------------------------------------------------------------------------
> loopoo's Profile: http://www.excelforum.com/member.php...o&userid=28792
> View this thread: http://www.excelforum.com/showthread...hreadid=484773
>
>