Update a date cell if changes are made two 2 other cells. Must function in 20 sections

Snake Eyes

Board Regular
Joined
Dec 14, 2010
Messages
118
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a sheet with 20 duplicate sections each representing it's own set of data.
I would like to have
Date Cell: AA68

automatically update with the current date and time (I do know this cell formatting), if any changes are made to

Edit Cells: AA68 or AA71 or AA76.

I'd like to repeat this functionality with the other 19 sections.
What I've found is based on code applied to the sheet and consequently limits this to only one section with limitations in functionality.

Here's the next couple...
Date Cell: AA140
Edit Cells: AA145, AA148, AA153

Date Cell: AA217
Edit Cells: AA22, AA225, AA230

TY
 
I can only foresee a vba code solution - because I'm weak in formulas. In the workbook open event I'd get the values from the 20 date cell addresses from a predefined array ary1=(AA140, AA217...) and store the values somewhere. Maybe another array (say, ary2) rather than 20 variables. Then in a Select Case block in the sheet change event (using AA140 as an example, and in English):
VBA Code:
Select Case Target.Address
    Case "AA140"
        For i = 0 to Ubound(ary1) - 1
            If ary1(i) <> ary2(i) Then 
                blnSame = False
                'now set address field to today's date
            End If
        Next
Of course, that is pseudo code and it's likely I don't fully understand the problem:
I would like to have
Date Cell: AA68 automatically update with the current date and time (I do know this cell formatting), if any changes are made to ???
Edit Cells: AA68 or AA71 or AA76.
The last line of that is confusing. You wouldn't want to automatically update a cell and also be allowed to edit it. Or would you?
Also, if this is an example of what you actually want to edit (Edit Cells: AA145, AA148, AA153) then the code approach I posted is of no use. I took your issue to be that you want to check if any cell in your edit groups had been changed. If so, edit only the date cell.
 
Upvote 0
Sorry for the confusion Micron. Maybe this small edit will clarify my issue.
I would like to have
Date Cell: AA68 automatically update with the current date and time (I do know this cell formatting), if any changes are made to the Edit Cells: AA68 or AA71 or AA76.

The 4 cells referenced represent one section for which I would like this functionality.
Independently and within the same worksheet, I would like this to work for 19 other sections.

These two groups of cells represent only two of the other 19 sections. Once I get a working solution, I can apply it to the remaining 17 sections. I include them here for reference.
Date Cell: AA140
Edit Cells: AA145, AA148, AA153

Date Cell: AA217
Edit Cells: AA22, AA225, AA230
 
Upvote 0
Then I think what I posted is how I'd approach it. Two edit groups have increments of 3 then 5 ( like 145 to 148, then 148 to 153). The 3rd posted group increments as 203, 5. If that's a mistake and the pattern is really +3 then +5 for all groups, that might simplify things a bit.
You said you had code already, so adapt that to use the arrays I mentioned? Or at least adapt according to the logic I imagine for this?
 
Upvote 0
Following up on Micron's search for a pattern, are you sure you don't have 2 typos in your sample data ?
In the first example you have AA68 as both the logging date cell AND the triggering cell (that's effectively a circular reference). Based on the other examples I would expect the Date cell to be AA63.
In the last example in the 200 range you have a cell AA22, I imagine this should be AA222.
Can you check and confirm ?
 
Upvote 0
Try this code (right click on tab name, then View code, then paste this code into
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Address(0, 0)
    Case "AA68", "AA71", "AA76"
        Range("AA65").Value = Now
    Case "AA222", "AA225", "AA230"
        Range("AA217").Value = Now

    ' other cases put here...

End Select
Application.EnableEvents = True
End Sub
 
Upvote 0
Following up on Micron's search for a pattern, are you sure you don't have 2 typos in your sample data ?
In the first example you have AA68 as both the logging date cell AND the triggering cell (that's effectively a circular reference). Based on the other examples I would expect the Date cell to be AA63.
In the last example in the 200 range you have a cell AA22, I imagine this should be AA222.
Can you check and confirm ?
I did have a couple of typos. Sorry about that.
Date Cell: AA63
Edit Cells: AA68 or AA71 or AA76

Date Cell: AA140
Edit Cells: AA145, AA148, AA153

Date Cell: AA217
Edit Cells: AA222, AA225, AA230
 
Upvote 0
Then I think what I posted is how I'd approach it. Two edit groups have increments of 3 then 5 ( like 145 to 148, then 148 to 153). The 3rd posted group increments as 203, 5. If that's a mistake and the pattern is really +3 then +5 for all groups, that might simplify things a bit.
You said you had code already, so adapt that to use the arrays I mentioned? Or at least adapt according to the logic I imagine for this?
I'll try Micron's code when I get back to this over the weekend.
TY
 
Upvote 0
Try this code (right click on tab name, then View code, then paste this code into
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Address(0, 0)
    Case "AA68", "AA71", "AA76"
        Range("AA65").Value = Now
    Case "AA222", "AA225", "AA230"
        Range("AA217").Value = Now

    ' other cases put here...

End Select
Application.EnableEvents = True
End Sub
Thanks for this suggestion.
I'll try it and compare it to the other code posted and report back.
 
Upvote 0
I asked about changing the same cell as you'd edit in my first post :unsure:
Which way you go depends on what you mean by "if any changes are made". For example, if no one will ever change 23 to 21 in the cell or formula bar, and then realize their mistake and backspace and make it 23 again, then code like that in post 6 should work. Otherwise, after restoring 23 that code will edit your date cell whether you want it to or not. Mine idea was based on the notion that such activity in a cell doesn't constitute a change so it may be more complex than what you need if that wont' happen or you don't care.
 
Upvote 0

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
Back
Top