Frank Nunez
New Member
- Joined
- Aug 9, 2020
- Messages
- 3
- Office Version
- 2019
- Platform
- Windows
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
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