Tally Macro: Broken Code

First, it is not practical to troubleshoot this without your data. Can you provide a sample file with data that causes the problem?

Second, it would be much easier to read your code if you used indentation to show the control structure, like this:

Private Sub CommandButton4_Click()

   Dim NoTot As Integer
   Dim tot As Integer
   Dim oSheet As Worksheet
   Dim oOutput As Worksheet
   Dim total As Integer
   Dim oCell As Range
   Dim oSet As Long
   
   On Error Resume Next 'In case any sheet has no data in A
   Set oOutput = ThisWorkbook.Worksheets("ARG Home")
   For Each oSheet In ThisWorkbook.Worksheets
      Select Case oSheet.Name
         'Change sheet names (next line) to actual names
         Case "ARG Home", "Tracking Form"
         Case Else
            oSet = Application.Match("Completed", oSheet.Range("1:1"), 0) - 1
            tot = oSheet.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count - 1
            total = total + tot
            For Each oCell In oSheet.Range("A:A").Cells.SpecialCells(xlCellTypeConstants)
               If oCell.Row > 1 Then
                  If UCase(Trim(oCell.Offset(0, oSet))) = "YES" Then
                     'If cell value is "Yes" color should be NO FILL????
                     oCell.Offset(0, oSet).Interior.ColorIndex = xlColorIndexNone
                  Else
                     'If cell value is NOT "Yes" cell should have YELLOW ???.
                     oCell.Offset(0, oSet).Interior.ColorIndex = 6
                     NoTot = NoTot + 1
                  End If
               End If
            Next oCell
      End Select
   Next oSheet
   oOutput.Range("I33").Value = NoTot
   oOutput.Range("D33").Value = total
   
End Sub