Changing font.colorindex on non active sheet

I am bumping up against a problem with changing colorindex of a bunch of declared ranges. All works fines when the sheet containing these cells is the one active, when its not I get 1004 Unable to set the Colorindex property of the Font Class. I’m still learning but was wondering if it has anything to do with the range variables not being Global (or is that Public?)

At first I thought I may also have hidden certain rows in the Union ranges and was trying to change the colorindex whilst hidden. I don’t think it is as I set the sheet to all unhidden at the start of the routine. As an aside is it actually a problem attempting to change formatting on hidden rows?

As ever I am grateful for any direction/thoughts of the forum

Please Login or Register  to view this content.

Whenever you refer to simply Range("blah") it refers to the activesheet because you have not specified another one. If inside a With...End With block, you should use .Range("blah") because the . indicates that the range is a property of the object specified in the With statement. So, for example:

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.