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

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


(E-Mail Removed) wrote:
>
> 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

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


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
>
> 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

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

"Peter T" <peter_t@discussions> wrote:

>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
>
>
><(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>>
>>
>> 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, 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 <(E-Mail Removed)> wrote:

>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
>
>
>(E-Mail Removed) wrote:
>>
>> 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

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

(E-Mail Removed) wrote:
>
> 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 <(E-Mail Removed)> wrote:
>
> >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
> >
> >
> >(E-Mail Removed) wrote:
> >>
> >> 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

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 <(E-Mail Removed)> wrote:

>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
>
>(E-Mail Removed) wrote:
>>
>> 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 <(E-Mail Removed)> wrote:
>>
>> >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
>> >
>> >
>> >(E-Mail Removed) wrote:
>> >>
>> >> 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

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





(E-Mail Removed) wrote:
>
> 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 <(E-Mail Removed)> wrote:
>
> >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
> >
> >(E-Mail Removed) wrote:
> >>
> >> 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 <(E-Mail Removed)> wrote:
> >>
> >> >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
> >> >
> >> >
> >> >(E-Mail Removed) wrote:
> >> >>
> >> >> 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