Keeping the date fixed

  • Thread starter Thread starter Shivam.Shah
  • Start date Start date
S

Shivam.Shah

Hello,

I was wondering if I could get help to write a query for the scenario
where:

I enter a value in column A and a date appears in column B, which
stays fixed and does not change based on the current date. Also, if I
delete the value from Column A, then the date also gets deleted.

This is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub

The problem is, as I said, if I delete the value from column A, the
date still stays in column B, which I don't want it to.

Any help will be great.

Thanks very much!
 
This should do what you want...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
If Target.Value = "" Then
Target.Offset(0, 1).Value = ""
Else
Target.Offset(0, 1).Value = Now()
End If
End If
End Sub
 
Hi Rick,

Thanks very much!! Also, if I want to apply it to more than one pair
of columns, do I have to just copy and paste the code multiple times
and change the target column? Right now I am using it in just column A
and B, but I also enter values in column K and want dates in column L.
So, do I just copy and paste code again and change the target?

Thanks again,

Shivam
 
This is a more generalized version of the code I posted...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const ColumnsToCheck As String = "A:A,K:K"
If Not Intersect(Target, Range(ColumnsToCheck)) Is Nothing Then
If Target.Value = "" Then
Target.Offset(0, 1).Value = ""
Else
Target.Offset(0, 1).Value = Now()
End If
End If
End Sub

Just change the "A:A,K:K" in my assignment to the ColumnsToCheck constant to
include all the columns you want or need. So, for just Column A, you would
use...

Const ColumnsToCheck As String = "A:A"

For Columns A and K (as per your posting), you would use what I used in the
code above. If you wanted to add Column X to the mix, you would use this
assignment...

Const ColumnsToCheck As String = "A:A,K:K,X:X")

and so on.

--
Rick (MVP - Excel)


Hi Rick,

Thanks very much!! Also, if I want to apply it to more than one pair
of columns, do I have to just copy and paste the code multiple times
and change the target column? Right now I am using it in just column A
and B, but I also enter values in column K and want dates in column L.
So, do I just copy and paste code again and change the target?

Thanks again,

Shivam
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
If Target <> "" Then
Target.Offset(0, 1).Value = Now()
Else: Target.Offset(0, 1) = ""
End If
End If
End Sub
 
Back
Top