Change Event (How post Date/Time to Changed Cells Rows but different Column)

[EagleOne]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
MsgBox "Range " & Target.Address & " was changed."
End Sub


Using the above Sub:

Facts: Range("A1:A5") values have been changed

Goal: Need VBA to change Range("H1:H5") with Now()

Challenge: What if more than one cell is changed?

In short, if Target.Address = Range("A1:A5, A8")
What VBA code would change Target.Address Rows 1,2,3,4,5,8 but same rows in Column "H"?

TIA EagleOne

 

[Dave Peterson]

You could loop through the cells in the range that changed (limited to just the
range you're interested in, too).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myIntersect As Range
Dim myCell As Range
Dim myRngToCheck As Range

Set myRngToCheck = Me.Range("A1:A5") 'the area I care about

Set myIntersect = Intersect(Target, myRngToCheck)

If myIntersect Is Nothing Then
Exit Sub 'outside my range
End If

Application.EnableEvents = False
For Each myCell In myIntersect.Cells
With Me.Cells(myCell.Row, "H")
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
Next myCell
Application.EnableEvents = True

End Sub

 

[Peter T]

Not sure I understand the post (very confusing!) and do you mean "A1:A5" or
"A1:A5, A8"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Const cAdr As String = "A1:A5, A8"

On Error Resume Next
Set rng = Intersect(Range(cAdr), Target)
On Error GoTo errExit

If Not rng Is Nothing Then
'MsgBox "Intersect changed " & rng.Address
rng.Offset(, 7).Value = Now
End If


Exit Sub
errExit:
End Sub

Regards,
Peter T

 

[EagleOne]

Sorry for the confusion. I added A8 to the "mix" to throw in the concept of non-contiguous range.

In reality, A1:A5, A8 could be any cells in the worksheet.

Therefore the range is Target.Address. Where the Column to post the Date/Time is fixed but the
Target.Address Rows.

I hope I cleared it up.

Thanks for your time and knowledge. EagleOne

 

[EagleOne]

Dave, I was not real clear.

That said I know how to proceed.

In actuality, it is only the Target.Address cells (Rows) that I need to isolate.
Not specifically Range("A1:A5") which was used as a example.

Thanks EagleOne

 

[Dave Peterson]

I don't understand.

If you want the row of each cell in the target, you could loop through the
cells.

If you want the starting row of the first area in the target, you could loop
through the areas.

msgbox target.areas(1).row

Or the first row in each area:

Dim myArea as range
for each myarea in target.areas
msgbox myarea.row
next myarea

 

[EagleOne]

Dave I just realized that you had a question,

The solution via your help was:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'
'
Dim myCell As Range
Dim myRange As Range
Dim myTime As String

Set myRange = Range(Target.Address)
'Debug.Print "Cells Changed: " & myRange.Address
Application.EnableEvents = False
myTime = Now()
For Each myCell In myRange
With Me.Cells(myCell.Row, "O")
'.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = myTime
End With
Next myCell
Application.EnableEvents = True
End Sub


Thanks Dave

 

[Dave Peterson]

Set myRange = Range(Target.Address)
'Debug.Print "Cells Changed: " & myRange.Address
Application.EnableEvents = False
myTime = Now()
For Each myCell In myRange

You don't need that myRange variable. Or the Set command.
For Each myCell In target.cells
(I like the .cells property. I find it self-documenting.)

So you're looking for a change in any old cell in the worksheet? It doesn't
matter what column the change is made in (or what columns the changes are made
in)?

And if you change a bunch of areas,
$B$5:$L$11,$T$8:$Z$13,$AD$6:$AK$9,$AJ$12:$AP$18,$S$17:$T$23,$Y$20:$AE$25,
$AS$22:$AY$30,$F$26:$J$30,$H$15:$L$20,$K$34:$K$36,$R$34,$R$34:$AA$37
(in one fell swoop--like clearing the contents or using ctrl-enter to fill the
cells)

You'll be processing all 418 cells--even though you've only changed 30 rows
(5:30,34:37).

If you wanted, you could limit the range and just use that to determine the rows
that changed.

Here's one that may get you started (I just posted this for a different
question):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToChk As Range
Dim myIntersect As Range
Dim myOneColRng As Range
Dim myCell As Range

Set myRngToChk = Me.Range("A5:cb3000") 'me.cells 'for all the cells

Set myIntersect = Intersect(Target, myRngToChk)

If myIntersect Is Nothing Then
Exit Sub
End If

'just one cell per row that got a change
Set myOneColRng = Intersect(myIntersect.EntireRow, Me.Columns(1))

Application.EnableEvents = False
For Each myCell In myOneColRng.Cells
With Me.Cells(myCell.Row, "O")
.NumberFormat = "dd-mmmm-yyyy hh:mm:ss"
.Value = Now
End With
Next myCell
Application.EnableEvents = True

End Sub