Autofilling the Date as "TODAY" when making a change in an adjacent cell

Wilkesy13

New Member
Joined
Jul 28, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
Can anyone help with my Excel/VBA question as below? I am a VBA virgin and so even entering the text into the code window is alien to me.

I have created a spreadsheet with various columns but essentially I have a list of staff members that I have to perform various safety checks on. Currently with my spreadsheet once I have completed a safety check I select a name from a drop down list in column E, this then changes Column C from showing "OPEN" to "CLOSED." With this change I'd like the date to automatically enter the adjacent cell in column I.

Is this possible for an idiot like me?

Any help would be so GREATLY appreciated
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,820
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Right-click on the sheet tab name at the bottom of your sheet, select "View Code", and paste this VBA code in the resulting VB Editor Window:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at onc
    If Target.CountLarge > 1 Then Exit Sub
    
'   Only run if column E updated and is not blank
    If Target.Column = 5 And Target <> "" Then
'       Put date in column I
        Target.Offset(0, 4) = Date
    End If
    
End Sub
This will automatically put today's date in column I whenever a value is entered in column E.
 
Solution

Wilkesy13

New Member
Joined
Jul 28, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Right-click on the sheet tab name at the bottom of your sheet, select "View Code", and paste this VBA code in the resulting VB Editor Window:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at onc
    If Target.CountLarge > 1 Then Exit Sub
   
'   Only run if column E updated and is not blank
    If Target.Column = 5 And Target <> "" Then
'       Put date in column I
        Target.Offset(0, 4) = Date
    End If
   
End Sub
This will automatically put today's date in column I whenever a value is entered in column E.
This is amazing!

Thank you so much for your help. Even I was able to get this to work with your help.

Can't thank you enough!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,820
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
:)
 

Wilkesy13

New Member
Joined
Jul 28, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are welcome.
Glad I was able to help!
:)
I don't know if this is the right place for this but would you have any suggestions on how I can broaden/improve my knowledge of macros and VBA? I feel bad to constantly ask for help if it's something that is relatively simple.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,820
Office Version
  1. 365
Platform
  1. Windows
I don't know if this is the right place for this but would you have any suggestions on how I can broaden/improve my knowledge of macros and VBA? I feel bad to constantly ask for help if it's something that is relatively simple.
It depends on your preferred style of learning.
There are plenty of good books out there (a few can be found at the MrExcel store). There are also lots of good articles and videos on line that you can find with Google searches.

And just scanning the various questions that are posted there, and look at the solutions is a helpful way to learn new things too.
You can also do a keyword search if you want to look for questions on particular topics.

And don't be shy about asking questions here!
The code I provided is known as "Event Procedure VBA code", which is code that runs automatically upon some event happening.
I would probably considerate "intermediate VBA skill". Most VBA noobs would probably not be familiar with it.
 

Wilkesy13

New Member
Joined
Jul 28, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
It depends on your preferred style of learning.
There are plenty of good books out there (a few can be found at the MrExcel store). There are also lots of good articles and videos on line that you can find with Google searches.

And just scanning the various questions that are posted there, and look at the solutions is a helpful way to learn new things too.
You can also do a keyword search if you want to look for questions on particular topics.

And don't be shy about asking questions here!
The code I provided is known as "Event Procedure VBA code", which is code that runs automatically upon some event happening.
I would probably considerate "intermediate VBA skill". Most VBA noobs would probably not be familiar with it.
Hi Joe,

Considering the extent and success of the last time you helped me I was wondering if there was a way you could look at my latest post to see if you can provide a solution? Please let me know if you have any questions. If you do get to take a look I want to thank you in advance. Any help is greatly appreciated!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,823
Messages
5,766,653
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top