Macro is skipping steps.

[Macro_Nerd99]

When I put a breakpoint and run through this macro manually everything works fine, but when I run it with a button it skips this step in the for-loop:
ThisWorkbook.Sheets(strTabName).Range("J" & thisrow).Interior.Color = Range("I" & thisrow).DisplayFormat.Interior.Color
This step is important in order for the SumByColor formula to work.
How can I fix this?

Public Sub Total()
    Application.ScreenUpdating = False
    Dim lr As Long
    Dim lr2 As Long
    Dim lr3 As Long
    lr = ThisWorkbook.Worksheets(strTabName).Cells(Rows.Count, "C").End(xlUp).Row
    Dim thisrow As Integer
 
 
    ThisWorkbook.Worksheets("Control2").Visible = True
 
    ThisWorkbook.Worksheets("Control2").Range(Team_Range2).Copy Destination:=Worksheets(strTabName).Range("I20")
  RangeCopyPic rng:=Selection, Appearance:=xlScreen, Format:=xlPicture
  Worksheets(strTabName).Range("I20").PasteSpecial
 
    ThisWorkbook.Sheets("Control").Range("A31:A39").Copy Destination:=Worksheets(strTabName).Range("L20")
       ActiveSheet.UsedRange.EntireColumn.AutoFit
      lr2 = ThisWorkbook.Worksheets(strTabName).Cells(Rows.Count, "I").End(xlUp).Row
       For thisrow = 20 To lr2
            Sheets(strTabName).Range("J" & thisrow).NumberFormat = "[h]:mm:ss"
            ThisWorkbook.Sheets(strTabName).Range("J" & thisrow).Interior.Color = Range("I" & thisrow).DisplayFormat.Interior.Color
            ThisWorkbook.Sheets(strTabName).Range("J" & thisrow).Formula = "=SUMIF($C$20" & ":$C$" & lr & ",I" & thisrow & ",$D$20:$D$" & lr & ")"
       Next

       ThisWorkbook.Sheets(strTabName).Range("M20:M28").NumberFormat = "[h]:mm:ss"
       ThisWorkbook.Sheets(strTabName).Range("J" & lr2 + 1).Formula = "=SUM(J20:J" & lr2 & ")"
       ThisWorkbook.Sheets(strTabName).Range("M20").Formula = "=SumByColor(J20:J" & lr2 & ",L20)"
       ThisWorkbook.Sheets(strTabName).Range("M21").Formula = "=SumByColor(J20:J" & lr2 & ",L21)"
       ThisWorkbook.Sheets(strTabName).Range("M22").Formula = "=SumByColor(J20:J" & lr2 & ",L22)"
 
       ThisWorkbook.Sheets(strTabName).Range("M23").Formula = "=SumByColor(J20:J" & lr2 & ",L23)"
        ThisWorkbook.Sheets(strTabName).Range("M24:M28").Merge
      '   Sheets(strTabName).Range("L19:M24").Copy Sheets(strTabName).Range("A1")
     
         ThisWorkbook.Sheets(strTabName).Activate
         ActiveSheet.Range("L20:M29").Select
        RangeCopyPic rng:=Selection, Appearance:=xlScreen, Format:=xlPicture
    
        ActiveSheet.Range("A1").Select
         ActiveSheet.PasteSpecial
         ActiveSheet.UsedRange.EntireColumn.AutoFit

  
     
     
       ThisWorkbook.Sheets(strTabName).Range("J37").Font.Bold = True
 
       ActiveSheet.UsedRange.EntireColumn.AutoFit
       Create_Headings
       ThisWorkbook.Sheets(strTabName).Rows("19:40").Hidden = True
   
     '  ThisWorkbook.Sheets(strTabName).Range("B2") = ThisWorkbook.Sheets(strTabName).Range("M20").Text
      ' ThisWorkbook.Sheets(strTabName).Range("B3") = ThisWorkbook.Sheets(strTabName).Range("M21").Text
       'ThisWorkbook.Sheets(strTabName).Range("B4") = ThisWorkbook.Sheets(strTabName).Range("M22").Text
       'ThisWorkbook.Sheets(strTabName).Range("B5") = ThisWorkbook.Sheets(strTabName).Range("M23").Text
       'ThisWorkbook.Sheets(strTabName).Range("B6:b10") = "0"
    
       Create_button2
       Range("A1").Select
        ActiveSheet.UsedRange.EntireColumn.AutoFit
       
           Application.ScreenUpdating = True
End Sub

 

[StephenCrump]

Your macro won't be skipping steps.

I suspect the problem is the unqualified reference to Range("I" & thisrow), so the code will use the ActiveSheet, whatever that happens to be.

From your other code, I'm guessing you actually want this to be Worksheets(strTabName).Range("I" & thisrow)?

(You're also qualifying most, but not all, range references with ThisWorkbook. But I assume your code is dealing with just the one workbook?)

 

[Macro_Nerd99]

Thanks for your response. When I put a worksheets(strTabname).activate before the loop, it worked great!

 

[6StringJazzer]

It would be better to qualify your references, possibly using With, instead of activating things to force the default. That is not reliable.