multiple range reference

[Mark J Kubicki]

??? (wrong number of arguments or invalid property assignment)
if user has selected a cell in any of these (3) ranges... do this...

Dim rngTimeEntry As Range
Dim rngProjectTotalHours As Range
Dim rngDateTotalHours As Range

rngTimeEntry = "E6:R" & ActiveSheet.Range("totalcellref").Row - 1
rngProjectTotalHours = "U6:U" & ...
rngDateTotalHours = "E" & CStr(ActiveSheet.Range("total...

If Not Intersect(Target, Range(rngTimeEntry, rngProjectTotalHours,
rngDateTotalHours)) _
Is Nothing Then
...
End If

 

[Dave Peterson]

But you're not treating those 3 variables as ranges.

You're treating them as strings--they hold the addresses of ranges.

You could continue to treat them as strings:

Dim rngTimeEntryAddr as string
dim rngProjectTotalHoursAddr as string
dim rngDateTotalHoursAddr as string

rngtimeentryaddr = "e6:r" & ActiveSheet.Range("totalcellref").Row - 1
'etc...

if not intersect(target, union(range(rngtimeentryaddr), _
range(rngprojecttotalhoursaddr), _
range(rngdatetotalhoursaddr)) is nothing then

....

Or you could treat them as range objects:

Dim rngTimeEntry As Range
Dim rngProjectTotalHours As Range
Dim rngDateTotalHours As Range

with activesheet
set rngTimeEntry =.range("E6:R" & .Range("totalcellref").Row - 1)
set rngProjectTotalHours = .range("U6:U" & ...)
set rngDateTotalHours = .range("E" & CStr(.Range("total...))

If Not Intersect(Target, _
union(rngTimeEntry, rngProjectTotalHours, rngDateTotalHours)) _
Is Nothing Then
...
End If