VBA Excel Write timestamp to cell on change of another cell

0 votes

In the event that the status (B3) changes, I want to put a timestamp (E3). At least 30 more instances of this type should follow in the worksheet. Only one example is presently supported by the code (Country1). Do you know how this might be put into practice?
I've already tried a few different sorts, but only "Country 1"—not "Country 1," "Country 2," "Country 3," etc.—worked.
Every third column is adjusted when I change the code for the "B3:I3" range. For instance, if I add a comment in column D3, a timestamp will be generated in column H3. I do not want that. :( 

Is there a way to adjust the code so that as soon as a change is made in the Status column (B3;F3;J3etc.), the Timestamp column (E3;I3 etc.) will reflect the time stamp?

3

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B3:B5"))  
Is Nothing Then Exit Sub  
Application.EnableEvents = False  
Target.Offset(0,3).Value = Now 
Application.EnableEvents = True  
Feb 5, 2023 in Others by Kithuzzz
• 38,000 points
1,050 views

1 answer to this question.

0 votes

Your request is a little vague, and it's possible that your post's table format was misinterpreted. The current time is being added by your code to a cell three columns away from the target cell. Because it is dynamic, if you set

If Intersect(Target, Range("B2:I3")) 

The value in cell 3, which is three columns away from the altered cell, will be returned to you. Use the target.row property if you always want column E to be updated.

Cells(Target.Row,5).Value = Now 

...to make the column static and the row dynamic. If this is not what you're searching for, please clarify your inquiry. What cell in cell F2 should you use to enter the timestamp if country2 is there?

answered Feb 5, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,651 views
0 votes
1 answer
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,600 points
1,133 views
0 votes
0 answers

Excel VBA: How to change order of scenarios

I'm trying to run a VBA loop ...READ MORE

Nov 27, 2022 in Others by Kithuzzz
• 38,000 points
368 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,260 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,697 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
975 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,090 views
0 votes
1 answer

Excel VBA to change background image of shape by clicking on shape

You need to keep track of what ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
2,997 views
0 votes
1 answer

Excel VBA: Trying to read all files in folder based on cell input and output to another cell

Your array has no capacity for data ...READ MORE

answered Jan 24, 2023 in Others by narikkadan
• 63,600 points
616 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP