Keeping the date fixed
 

Hello,

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

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!

Keeping the date fixed
 

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

--
Rick (MVP - Excel)


"Shivam.Shah" wrote in message
...
Hello,

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

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!

Keeping the date fixed
 

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



On Feb 12, 1:09*pm, "Rick Rothstein"
wrote:
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

--
Rick (MVP - Excel)

"Shivam.Shah" wrote in message

...

Hello,

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

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!

Keeping the date fixed
 

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)


"Shivam.Shah" wrote in message
...
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



On Feb 12, 1:09 pm, "Rick Rothstein"
wrote:
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

--
Rick (MVP - Excel)

"Shivam.Shah" wrote in message

...

Hello,

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

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!

Keeping the date fixed
 

Again, thanks a lot!! Your a lifesaver! Works perfect.

Wish you the best!

Shivam


On Feb 12, 1:40*pm, "Rick Rothstein"
wrote:
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)

"Shivam.Shah" wrote in message

...
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

On Feb 12, 1:09 pm, "Rick Rothstein"

wrote:
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

--
Rick (MVP - Excel)

"Shivam.Shah" wrote in message

...

Hello,

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

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!