Selecting a range variable in different worksheets within same workbook

I am having trouble figuring out how to do the following:

I want to make a range variable ("testrange") that I can use to define a range of non-continuous cells and then use that range variable to select that range of cells in different worksheets within the same workbook.

I tried the below code (simply coloring cells red), but get an error when I try to select "testrange" after moving to the next worksheet.

Please Login or Register  to view this content.

The next worksheet is also supposed to have the same range of cells colored red, but I can only get it to work on the first active sheet.

I would like to avoid having to repeatedly rebuild the range I want selected each time I move to a different worksheet.

I will appreciate any help on this.

testrange will be linked to the sheet you define it on, so when you move to the next sheet, it will be trying to select a range on a non-active sheet which would cause the problem. I would suggest something like:

Please Login or Register  to view this content.

Thank you for the reply, ragulduy.

Sadly, the range I am going to be selecting is going to be a whole lot bigger/longer than the example I gave (over 50 non-consecutive cell ranges) and I was planning to build that range using loops and Union.

My ultimate goal is to use the testrange to periodically consolidate the conditional formatting ranges for 30+ worksheets. Conditional formatting takes up a lot of file size space when there are duplicate rules covering several ranges (inevitably happens when copy-pasting-inserting), but much less so when there is only one instance of the rule covering all the ranges (this would be testrange).

Thus, given specified row #s and other conditions, testrange is going to have to change in size to fit what I need covered.

I guess I could try to convert my current dozens of conditional formatting rules to vba code, but I'd rather not if I could help it...
And wouldn't I still have the range problem...?

Hi shg!

I actually tried to define the range by string at first, but the string got too long and vba would not recognize the whole string (it truncates at some point). Things got really icky complicated when I tried to break the string into manageable substrings, so I fled from that idea and into the arms of range variables (which apparently do not have such a size limitation as string variables).

(This was also back when I thought ranges had to be defined by Column Letters and Row #s. I then found out about using Cells, which made it easier to define ranges solely by integers and formulas, as well as to enlarge using counter loops.)

I think that even if I can use strings to fill in a Range( Cells (row,column), Cells (row, column) ) format, I will still run into the limitation of string length problem.

Thank you for trying to help, though!

Maybe:

Please Login or Register  to view this content.

?