Thread: 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

Sub Rategenerator()

Dim startrate As Double
Dim ws As String
Dim penAge As Long
Dim cAge As Double
Dim cAgeWhole As Double
Dim r As Range
Dim young As Range
Dim pencells As Range       ' pension ages for published aged used in LOE multiplier section
Dim pencellsincrem As Range ' pension ages other than those published in tables used in LOE multiplier section
Dim penpenpub As Range ' pension ages published for pension section
Dim wholeagehide As Range ' rows to hide where whole year birthday is being used
Dim ar As Double
Dim i As Long

cAge = [ageAtTrial]
cAgeWhole = Round(cAge, 0) / cAge
penAge = [RetirementAge]
startrate = [activerate]
i = 59
ar = 0.5

Worksheets("intresult ").Unprotect Password:=""
Application.ScreenUpdating = False
Application.EnableEvents = False

' set ranges for formatting of cells

With Sheets("Intresult ")
Set r = Union(.Range("22:25"), .Range("69:72"), .Range("116:119"), .Range("163:166"), .Range("210:213") _
            , .Range("257:260"), .Range("304:307"), .Range("351:354"), .Range("398:401") _
            , .Range("445:448"), .Range("492:495"), .Range("539:542"))
            
Set young = Union(.Range("E28:f32"), .Range("B30:C30"))
Set pencells = Range("B18:C20,B25:C25,E16:f25,B28:C29,E35:f44")
Set pencellsincrem = Range("B18:C20,B25:C25,E16:F25,B28:C29,E35:f44")

Set penpenpub = Union(.Range("37:39"), .Range("44:44"), .Range("84:86"), .Range("91:91"), .Range("131:131"), .Range("138:138"), .Range("178:180") _
                    , .Range("185:185"), .Range("225:227"), .Range("232:232"), .Range("272:274"), .Range("279:279"), .Range("319:321") _
                    , .Range("326:326"), .Range("366:368"), .Range("373:373"), .Range("413:415"), .Range("420:420"), .Range("460:462"), .Range("467:467") _
                    , .Range("507:509"), .Range("514:514"))
                    
Set wholeagehide = Union(.Range("41:43"), .Range("53:55"), .Range("88:90"), .Range("100:102"), .Range("135:137"), .Range("147:149"), .Range("182:184") _
                    , .Range("194:196"), .Range("229:231"), .Range("241:243"), .Range("276:278"), .Range("288:290"), .Range("323:325") _
                    , .Range("335:337"), .Range("370:372"), .Range("382:384"), .Range("417:419"), .Range("429:431"), .Range("464:466"), .Range("476:478") _
                    , .Range("511:513"), .Range("523:525"))
End With


With Sheets("Intresult ")

    
    
' set default format for age interpolation cells
      r.EntireRow.Hidden = False
'      young.EntireRow.Hidden = False
      wholeagehide.EntireRow.Hidden = False
      penpenpub.EntireRow.Hidden = False
      young.Font.ColorIndex = 1
      young.Borders.ColorIndex = 1
      pencellsincrem.Font.ColorIndex = 1
      pencellsincrem.Borders.ColorIndex = 1
'      r.Font.ColorIndex = 1
'      r.Borders.ColorIndex = 1
      
' set Table 27 cells invisible if claimant not under 16
    If [Under16] = "no" Then
      young.Font.ColorIndex = 2
      young.Borders.ColorIndex = 2

     End If
End With

' set font and interior colour to white where claimants age is whole year and interpolation data not required in final calculation

With Sheets("Intresult ")

    If cAgeWhole = 1 Then
    
    
    Range("B22:C24").Font.ColorIndex = 2
    r.EntireRow.Hidden = True
    wholeagehide.EntireRow.Hidden = True
    
    Else
   
    r.EntireRow.Hidden = False
    wholeagehide.EntireRow.Hidden = False
    Range("B22:C24,E22:F24,B19:C19").Font.ColorIndex = 1
    End If

' set font and interior colour to white where extra interpolation data is not required in final calculation
    Select Case penAge

    Case 50, 55, 60, 65, 70, 75
    pencells.Font.ColorIndex = 2
    pencells.Borders.ColorIndex = 2
    penpenpub.EntireRow.Hidden = True

    Case Else
    penpenpub.EntireRow.Hidden = False
    pencellsincrem.Font.ColorIndex = 1
    pencellsincrem.Borders.ColorIndex = 1
    
    End Select
    
' paste loop for remaining multiplier rates

    For cycle = 1 To 11
    [activerate] = -2.5
    [activerate] = [activerate] + ar
    Sheets("Intresult ").Range("A12:g57").Copy
    Sheets("Intresult ").Range("A" & i).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Intresult ").Range("A" & i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        i = i + 47
        ar = ar + 0.5
    Next cycle
    
    Application.CutCopyMode = False
    [activerate] = startrate
  

   ActiveWindow.ScrollColumn = 1
   ActiveWindow.ScrollRow = 11
   
End With
    Application.EnableEvents = True
    Worksheets("intResult ").Protect Password:=""
    

End Sub

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:

With Sheets("Intresult ")
Set r = Union(.Range("22:25"), .Range("69:72"), .Range("116:119"), .Range("163:166"), .Range("210:213") _
            , .Range("257:260"), .Range("304:307"), .Range("351:354"), .Range("398:401") _
            , .Range("445:448"), .Range("492:495"), .Range("539:542"))
            
Set young = Union(.Range("E28:f32"), .Range("B30:C30"))
Set pencells = Range("B18:C20,B25:C25,E16:f25,B28:C29,E35:f44")
Set pencellsincrem = Range("B18:C20,B25:C25,E16:F25,B28:C29,E35:f44")

should be

With Sheets("Intresult ")
Set r = Union(.Range("22:25"), .Range("69:72"), .Range("116:119"), .Range("163:166"), .Range("210:213") _
            , .Range("257:260"), .Range("304:307"), .Range("351:354"), .Range("398:401") _
            , .Range("445:448"), .Range("492:495"), .Range("539:542"))
            
Set young = Union(.Range("E28:f32"), .Range("B30:C30"))
Set pencells = .Range("B18:C20,B25:C25,E16:f25,B28:C29,E35:f44")
Set pencellsincrem = .Range("B18:C20,B25:C25,E16:F25,B28:C29,E35:f44")