View Full Version : Solved: Define range and combine two codes

04-01-2008, 08:36 AM

Can someone please help me with following two things:

1) Change the range (Set rg = Range("A2:K5001") to dynamic range.
(Only the rows increase)
2) Combine both the codes into one.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range, rgRow As Range
On Error GoTo ExitHere
Set rg = Range("A2:K5001")
If Intersect(rg, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rgRow In Intersect(Target, rg).Rows
Cells(rgRow.Row, "L") = Now()
Next rgRow
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'check cells for desired format to trigger the calendarfrm.show routine
'otherwise exit the sub
Dim DateFormats, DF
DateFormats = Array("m/d/yy;@", "mmmm d yyyy")
For Each DF In DateFormats
If DF = Target.NumberFormat Then
If CalendarFrm3.HelpLabel.Caption <> "" Then
CalendarFrm3.Height = 191 + CalendarFrm.HelpLabel.Height
Else: CalendarFrm3.Height = 191
End If
End If
End Sub

Bob Phillips
04-01-2008, 09:09 AM
Why would you want to coimbine them, one is actioned on change, one on selection?

04-01-2008, 09:18 AM
I am sorry, I think it would be ok to keep it this way.

I would like to know about the dynamic range however. Thanks xld.

Bob Phillips
04-01-2008, 10:20 AM
How about simply

Set rg = Range("A:K")

04-01-2008, 11:15 AM
Great!! Thanks so much.

04-01-2008, 01:39 PM
Hi U,
Please remember to use the VBA button to format posted code.

04-01-2008, 01:48 PM
Got it. Thanks.