Current dates in cells C2:U65000 when values in cells range A2:A65000 changes

[sumit dey]

Hello everyone,

This is my first thread in vbaexpress and i need some serious help here.

I have a workbook...A very big workbook indeed with around 40+ columns out of which 12+ columns only contains dates whenever the last change occurs to a particular cell the dates changes in any one of the cell.

Eg,
Cell range B2:B65000 have data validation list containing around 12+ number of items.
Now whenever people select item 1 in B2 the corresponding cell U2 shall get updated with current date and the date should not change again now, when I again select item 2 in B2 the cell D2 should show the current date, when I select item 3 cell E3 should show current date and so on...upto cell U2

I tried the "if" function but, the dates in respective cells changes when I change value in cell B2 dropdown list.

I know vba can do this trick for me but, I am very bad in vba.
If anybody can help me with this I shall be very thankful.

Looking for your replies. My workbook is quite large so, can't attach it for now.

Thanks in advance...

[SamT]

Current dates in cells C2:U65000

Cell range B2:B65000 have data validation list containing around 12+ number of items.
Now whenever people select item 1 in B2 the corresponding cell U2 shall get updated with current date and the date should not change again now, when I again select item 2 in B2 the cell D2 should show the current date, when I select item 3 cell E3 should show current date and so on...up to cell U2

Change Value in B2, put date in U2, D2, E3. Dates in C to U. 12 items, 21 date columns.
I don't understand.

[sumit dey]

Current dates in cells C2:U65000

Change Value in B2, put date in U2, D2, E3. Dates in C to U. 12 items, 21 date columns.
I don't understand.

Hi SamT,

Thanks for your reply..

What I want is that whenever a value in B2 changes a cell gets current date which doesn't changes again and when we change the value in B2 again another cell gets current date and remains intact.

If B2 changes then C2 updates with current date...when B2 changes again this time D2 updates to current date...when B2 changes again E2 gets current date and so on...

Column B should have the list of items and columns C to U will have dates as per value changes in their corresponding cells in Column B
hope this helps.


Thanks
sumit

[SamT]

Put this code in the Worksheet code page. t only works with empty cells in C2 to U2

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$B$2" Then SetDate
End Sub

Private Sub SetDate()
Dim DatesRange As Range
Dim DateCellsCount As Long
Dim DatesCount As Long

  Set DatesRange = Range("$C$2:$U$2")
  DateCellsCount = DatesRange.Columns.Count
  DatesCount = WorksheetFunction.CountA(DatesRange) + 1
  
  If DatesCount < DateCellsCount Then DatesRange.Cells(DatesCount) = Now
End Sub

[sumit dey]

Hi SamT,

This is working exactly what I wished for...but, I guess it can work for the entire column B....currently it only works for the target cell B2...can it work for the range B2:B65000 so, when we change value in B3 date updates in cells C3 to U3 and when we change value in B4 date updates in C4 to U4 ....B65000 then date updates in C65000 to U65000.

I have one more query... what if I want dates in cells which are not adjacent to each other say dates only updates in random cells say C2, F2, G2, J2, K2 and other. Each cell is in a different column randomly and not in sequence.

Thanks again for this wonderful code...

[SamT]

B2 to end and Random cells. to end. Which one do you really want.

[sumit dey]

Hi SamT,
I know I am a bit confusing here...

The target cells will be B2 to end and the dates will be in Random cells to end...
when value in Column B changes dates updates should happen in columns say C, F, G, J, M, N, O, P and other approx 17 columns should have dates.

Target cell range B2:End and DatesRange should be C2:End, F2:End, G2:End, J2:End, M2:End, N:End,O2:End and other....

Hope I have clear any doubts....

[SamT]

No doubts, but I gotta know all the columns.

Like this

("C", "F", "G", "J")