Excel 2013 - Increment Date by 1 year using a Macro/Button

I have a spreadsheet that I've created with conditional formatting based on how close a domain is to it's expiry date.

Here it is:

Domain Renewal Spreadsheet
I've cracked the conditional formatting, but as it stands I still have to modify the date by hand once the renewal has been processed. I want this automated with a button via a macro if possible. I've attached a screenshot of the button and explained the functionality required. I need each button to increment the date by 1 year in that row.

The thing I'm most stumped on, is how to replicate each button rather than do each one by hand! :-(

Thanks in advance!
Who is Participating?
FamousMortimerConnect With a Mentor Commented:
Good morning.

Just a thought... You can use one button that will add a year to column D in the row that is selected with the following code.

Private Sub CommandButton1_Click()
    Cells(Selection.Row, 4).Value = DateAdd("yyyy", 1, Cells(Selection.Row, 4).Value)
End Sub

Open in new window

If you really want to have a button for each row, you can capture the bottom right cell of the button with this code


I dont really recommend this though.
UncleVirusAuthor Commented:
Brilliant, thank you! I've implemented it as follows: I've pruned all of our data from the spreadsheet and will upload here. It may be potentially useful for someone else.

The spreadsheet will warn you of expiring domains within 3 months and turn them orange :-)

(Please note, it contains Macro's for the buttons!)

UncleVirusAuthor Commented:
Brilliant, bang on the money! :-)
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Thanks, glad to help!

Also, in my post, i made it seem like you have to select the whole row, but as long as any cell in the row you wish to change is selected, it will work.
UncleVirusAuthor Commented:
Yeah I worked that one out, no sweat :o)

The only slightly annoying thing is that Excel doesn't allow you to Alt-Z/Undo the actions of a Macro! Not a major biggie but this has helped us out no-end. Embarassingly, I sysadmin hundreds of systems but profess to being a complete Excel beginner!
Yeah, that is a peeve of mine too.  In some of my programs I've actually saved a copy of the file before the macro runs if it has the potential to cause problems, to overcome the inability to Undo macro operations.  Usually though, I suppose the macro should be fool proof, but we are all human.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.