Clear cells depending on data validation list output

Hi all,

Hoping your combined geniusnesseses will be able to help me out with this - have had a good trawl through the forum and not found an answer which is enough to help me get this done.

So,

I have a workbook with data in (rows of people with details). I have a column (f) related to activity status - if a person leaves, the user can select active or vacant (using a validation lookup list). What i would like to do is clear the cells in column G, H and L for just that relevant row.

Any thoughts would be extremely gratefully received.

Many thanks,

Fergcu

Hi

The code below will clear the cells in G, H & L on the active row if the value of F is "Vacant".

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Columns(6)) Is Nothing Then

If ActiveCell.Value = "Vacant" Then
Cells(ActiveCell.Row, 7).ClearContents
Cells(ActiveCell.Row, 8).ClearContents
Cells(ActiveCell.Row, 12).ClearContents
End If
End If
End Sub

The code needs to be placed in the sheet module in order to function (select the sheet tab, right click and click on view code).

Regards

Jeff

Jeff,

absolutely fantastic, thank you - i had tried using the intersect method, but was obviously using it incorrectly, as i could only get it to work on one specific row.

Many thanks for this!

Kind regards,

fergcu