This is a discussion on set range as multiple ranges VBA within the Excel Questions forums, part of the Question Forums category; Im trying to use multiple ranges within the range 'Multirange', but have got something wrong. Was all working fine before ...
Im trying to use multiple ranges within the range 'Multirange', but have got something wrong. Was all working fine before with a single range.
[CODE]Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range
Dim MultiRange As Range
Dim isect As Range
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) = "B2" Then Call TitleCheck
If Target.Address(False, False) = "F2" Then Call DateCheck
Set r1 = Range("A11:G23")
Set r2 = Range("A26:G34")
Set r3 = Range("A38:G46")
Set r4 = Range("A50:G58")
Set MultiRange = Union(r1, r2, r3, r4)
Set isect = Intersect(Target, MultiRange)
If isect Is Nothing Then Exit Sub
If Application.WorksheetFunction.CountIf(MultiRange, Target) = 1 Then
Set MyActiveCell = ActiveCell
Application.CutCopyMode = False
ActiveSheet.Name = Target.Value & " Timesheet"
Application.ScreenUpdating = True
Please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: http://www.mrexcel.com/forum/showthread.php?t=99490).
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"