Constructing a Range from multiple named ranges returned cell/column values

[Frank Nunez]

Hi All! Thanks in advance for any advice, I'm stumbling on something I feel like I should be able to find reference to online, but I'm coming up short...

I have a function colorCells(myRange as Range), which runs conditional formatting operations on a range of cells. The range of cells can be up to 1000 rows and up to 81 columns wide. I'm trying to create an alternative to using traditional conditional formatting rules, as they break up range references if rows and columns are moved frequently, which they are in my workbook.

First, as this function will be called frequently to update conditional formatting in the workbook, so it needs to be ultra fast:
Should I populate an array and then pass that array to my function, or should I pass a range variable for the cells for the function to act upon, then populate the array in the function?

Second, VBA has to figure out the range of cells to be acted upon using returned row and column values from (4) named ranges in the workbook, who's positions may change as per the column and rows being moved around.

Take for example:
SC = Range("namedRange1").Column 'Starting Column in Range
SR = Range("namedRange2").Row 'Starting Row in Range
EC = Range("namedRange3").Column 'Ending Column in Range
ER = Range("namedRange4").Row 'Ending Row in Range
So if the above returned values could be reconstructed into a reference to "A3:C5", which can then be passed to my colorCells function, what is the correct syntax, either using cells() or Range()?

Thanks in advance everyone, much appreciated!
Frank

 

[offthelip]

Is there any reason you can't just use:

Set myrange = Range(Cells(sr, sc), Cells(er, ec))

 

[Frank Nunez]

Hi offthelip, thanks for that, I suppose I can, I guess I was asking also if that is the best method because I want to rewrite a lot of macros to use dynamic referencing in this way. Also, if the array should be populated prior to passing to the function or in the function, for speed? Thanks for the response, if that's the best method, I'll use it!

 

[offthelip]

Formatting is always going to be very slow, so the time setting a range is going to be negligible in comparison. I always try to design my workbooks to avoid using formatting for anything that changes, specifically because it is very slow to change with VBA. i.e I would always use additional helper columns to control conditional formatting so that I don't need to change it. It is much faster to set a load of values into a range than change the foramt

 

[Frank Nunez]

awesome thank you!

 

[Fluff]

Cross posted Dynamic Columns Range vs Cells in VBA

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.