Vba code to capture date while entering data in cell

[fazil1985]

Hi All,
I am bit new to vba coding in excel. Prsently I am trying create an excel sheet which capture the date once the data has been entered in a cell and the date should change.some how manage to get below vba code to capture the date of one column.which works perfectly. Presently what I need is to capture date of another cell column too from same sheet.
Following is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyDatas As Range
Dim MyDataRngs As Range
Set MyDataRngs = Range("R3:R1000")
If Intersect(Target, MyDataRngs) Is Nothing Then Exit Sub

On Error Resume Next

If Target.Offset(0, -6) = "" Then

Target.Offset(0, -6) = Now

End If



'Target.Offset(0, 2) = Now
For Each MyDatas In MyDataRngs

If MyDatas = "" Then

MyDatas.Offset(0, -6).ClearContents

MyDatas.Offset(0, -6).ClearContents

End If

Next MyDatas

End Sub

 

[Joe4]

Welcome to the Board!

Currently, it looks like your code is watching for an update in the range R3:R1000, and if one happens, and if column K of that same row is blank, enter the current date time in that cell.
Unless the entry in column R is blank. If so, then remove the entry from column K.

So what other columns do you want to apply this logic too?
What column to watch for updates, and which column to add current date/time too?

Also, please use code tags when posting your code in the future. It makes it much easier to read.
See here: How to Post Your VBA Code

 

[fazil1985]

Welcome to the Board!

Currently, it looks like your code is watching for an update in the range R3:R1000, and if one happens, and if column K of that same row is blank, enter the current date time in that cell.
Unless the entry in column R is blank. If so, then remove the entry from column K.

So what other columns do you want to apply this logic too?
What column to watch for updates, and which column to add current date/time too?

Also, please use code tags when posting your code in the future. It makes it much easier to read.
See here: How to Post Your VBA Code

Thanks Joe,
For your kind reply.i am just new here small hickups are there.As advised will add the codes tags.

Now regarding the code what I am trying add is when column O.if column O3 data is entered I want it capture the date in B3.

If could show me sample if would be grate help.

 

[Joe4]

Try this code and see if it does what you want. I added documentation to it to explain what it is doing along the way:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit sub if multiple cells updated at once (i.e. deletion or row)
    If Target.CountLarge > 1 Then Exit Sub

'BLOCK 1: Check column R, update column K
'   First check for change in column R
    Dim MyDataRngs As Range
    Set MyDataRngs = Range("R3:R1000")
    If Not Intersect(Target, MyDataRngs) Is Nothing Then
'       Check to see if value entered or erased
        If Target.Value = "" Then
'           If value in column R is erased, clear column K
            Target.Offset(0, -6).ClearContents
        Else
'           If value in column R but not in K, update column K
            If Target.Offset(0, -6) = "" Then
                Target.Offset(0, -6) = Now
            End If
        End If
    End If
    
'BLOCK 2: Check column O, update column B
'   First check for change in column R
    Dim MyDataRngs2 As Range
    Set MyDataRngs2 = Range("O3:O1000")
    If Not Intersect(Target, MyDataRngs2) Is Nothing Then
'       Check to see if value entered or erased
        If Target.Value = "" Then
'           If value in column O is erased, clear column
            Target.Offset(0, -13).ClearContents
        Else
'           If value in column O but not in B, update column B
            If Target.Offset(0, -13) = "" Then
                Target.Offset(0, -13) = Now
            End If
        End If
    End If

End Sub

 

[fazil1985]

Its really worked.thank you so much for your support.

 

[Joe4]

You are welcome.
Glad I was able to help!