Cant delete contents of a cell ... VBA issue

[Southernwave]

This should be simple.....

I have a sheet that ..when a entry is made in column B, the date is entered in column H.. all that works well until I found that if I remove the data in column B-G, the date in H stays.. and everytime I delete it the macro below runs and puts the date back... and I don't really want it to work that way.

Here is the code I am currently using

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
    If Target(1, 1).Row > 1 Then
       With Cells(Target(1, 1).Row, "h")
           .Value = Date
        End With
        End If
   ActiveSheet.Protect
   End Sub

Any suggestions ?

Cheers

Andrew

[mana]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Dim c As Range
    
    Set r = Intersect(Target, Columns("B"))
    If r Is Nothing Then Exit Sub
     
    ActiveSheet.Unprotect
    Application.EnableEvents = False
    For Each c In r
        If c.Row > 1 Then
            With c.EntireRow
                If WorksheetFunction.CountA(.Range("B1:G1")) > 0 Then
                    .Range("H1").Value = Date
                Else
                    .Range("H1").ClearContents
                End If
            End With
        End If
    Next
    Application.EnableEvents = True
    ActiveSheet.Protect
'
End Sub

[Southernwave]

Hi Mana and thank you for the quick reply. I have checked it on the sheet .. and also in a new book but it doesn't seems to either add a date or remove when I empty the cell in column B.

I will keep looking ... but thank you again

[snb]

Private Sub Worksheet_Change(ByVal Target As Range) 
  If Target.Row > 1 and target.column=2 Then
    ActiveSheet.Unprotect         
    for each cl  in target
      cl.offset(,6)= iif(cl.value="","",date) 
    next
    ActiveSheet.Protect
  End If 
End Sub

[Bob Phillips]

If you protect the sheet, as you do in your code, you can't make a change to any cells in that sheet, so the code never fires. You need to unlock cells that you can modify.

[Southernwave]

SNB... That works .. many thanks