Using Range(Cells() Cells()).Style results in appl

Question

Curretly I'm trying to style a range of cells if certain criteria are met. I am able to sucessfully apply a style to one cell, but have so far been unsuccessful in applying this to a range. Here's what I know works:

ElseIf OneA / OneC > 0.8 And OneA / OneC <= 1 Then
      ActiveWorkbook.Sheets(StartYear & " " & StartQ & " - " & EndYear & " " & EndQ).Cells(Employee, StartCol).Style = "60% - Accent2"

This above code styles the cell (Employee, StartCol) to 60% - Accent2. However, when I change this code to try to apply the styling to a range, I get the "Application defined or user defined error". Here's what I tried to change the code to:

ElseIf OneA / OneC > 0.8 And OneA / OneC <= 1 Then
                    ActiveWorkbook.Sheets(StartYear & " " & StartQ & " - " & EndYear & " " & EndQ).Range(Cells(Employee, StartCol), Cells(Employee + 49, StartCol)).Style = "60% - Accent2"

I'm not sure what I'm doing wrong here. I think it may be a syntax error, but I'm not experienced enough with vba to know where exactly it's going wrong. Any help would be greatly appreciated. Thanks!

Answer 1

You are using the Cells global object in the second snippet, but explicitly referencing the .Cells property of the worksheet in the first snippet. The second one will actually work, but only if the active worksheet matches the one you're getting from ActiveWorkbook.Sheets(). Grab a reference to the target worksheet, and then use its .Cells property instead of the global:

ElseIf OneA / OneC > 0.8 And OneA / OneC <= 1 Then
    Dim target As Worksheet
    Set target = ActiveWorkbook.Sheets(StartYear & " " & StartQ & " - " & EndYear & " " & EndQ)
    With target
        .Range(.Cells(Employee, StartCol), .Cells(Employee + 49, StartCol)).Style = "60% - Accent2"
    End With