Hello everyone,
I'd like to start by saying I have been using this forum for years and have always found not only the answers to my questions, but the community at large is willing to earnestly help.
Even with the more 'silly' requests, so I'd like to thank you, in advance for taking the time to read (and hopefully) respond with a solution.
In short, I'd like to pick a cell and know the following;
a) if the cell is already part of a merged group of cells
b) if the the cell IS part of an existing merged group, Id like that merged range returned (or at least the address of it)
The context here is I am displaying several grouped cells as a merged range on a worksheet. (its for staff shift times, say 10am ~ 1pm is represented as a collection of cells, the row is the same but columns are grouped to display a duration) From time to time, there may be overlapping ranges(say another 'shift' from 11am ~ 12pm), I'd like to detect and display when these overlaps occur but need to understand where the 'newest' range overlaps a previously rendered range.
something like;
The worksheet I am writing does alot more than I have stated here, suffice to say the data is dynamically read in from a network location and displayed.
So while the format of the data is consistent, the overlapping shifts are never known until runtime.
Again, any help would be greatly appreciated.
Regards,
BenR
I'd like to start by saying I have been using this forum for years and have always found not only the answers to my questions, but the community at large is willing to earnestly help.
Even with the more 'silly' requests, so I'd like to thank you, in advance for taking the time to read (and hopefully) respond with a solution.
In short, I'd like to pick a cell and know the following;
a) if the cell is already part of a merged group of cells
b) if the the cell IS part of an existing merged group, Id like that merged range returned (or at least the address of it)
The context here is I am displaying several grouped cells as a merged range on a worksheet. (its for staff shift times, say 10am ~ 1pm is represented as a collection of cells, the row is the same but columns are grouped to display a duration) From time to time, there may be overlapping ranges(say another 'shift' from 11am ~ 12pm), I'd like to detect and display when these overlaps occur but need to understand where the 'newest' range overlaps a previously rendered range.
something like;
VBA Code:
public function CheckRange (RnG as range) as range
If RnG.MergeCells = true then
' if the RnG range is part of a larger range, I would like the parent range returned
set CheckRange = RnG.ParentRange
else
' if the RnG is a stand alone (not part of an existing merged range, then just return the original range)
set CheckRange = RnG
end if
end function
The worksheet I am writing does alot more than I have stated here, suffice to say the data is dynamically read in from a network location and displayed.
So while the format of the data is consistent, the overlapping shifts are never known until runtime.
Again, any help would be greatly appreciated.
Regards,
BenR