Fixing Cell Spacing

[poetdoc]

First of all, all I know about VBA is how to spell it.

However, I have a chunk of code that is supposed to automatically enter today's date in cell M when "Yes" or "No" is clicked from a drop box in cell K. Here it is:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range
' Check whether cells in K2:K150 have changed
If Not Intersect(Range("M2:M150"), Target) Is Nothing Then
' Temporarily turn off screen updating
Application.ScreenUpdating = False
' Loop through all changed cells
For Each rngCell In Intersect(Range("M2:M150"), Target)
' Is the cell empty?
If rngCell = "" Then
' Clear the cell next to it
rngCell.Offset(0, 1).ClearContents
Else
' Enter the current date in the cell next to it
rngCell.Offset(0, 1) = Date
End If
' On to the next cell
Next rngCell
End If
' Turn screen updating on again
Application.ScreenUpdating = True
End Sub

I think I know the problem is the "Clear the Cell next to it" and the "Enter the current date in the cell next to it" parts. The cells have a field in the middle (L, obviously) that it would make no sense to move. How can I fix this so instead of filling cell M puts the date in N, entering in cell K would populate M? I'm sure it's embarrassingly simple.

Thanks!

 

[VoG]

Shouldn't it be?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range
' Check whether cells in K2:K150 have changed
If Not Intersect(Range("K2:K150"), Target) Is Nothing Then
' Temporarily turn off screen updating
Application.ScreenUpdating = False
Application.EnableEvents=False
' Loop through all changed cells
For Each rngCell In Intersect(Range("K2:K150"), Target)
' Is the cell empty?
If rngCell = "" Then
' Clear the cell next to it
rngCell.Offset(0, 2).ClearContents
Else
' Enter the current date in the cell next to it
rngCell.Offset(0, 2) = Date
End If
' On to the next cell
Next rngCell
End If
' Turn screen updating on again
Application.ScreenUpdating = True
Application.EnableEvents=True
End Sub

 

[poetdoc]

Perfect! Thank you!