How do I convert my code to not use "Select"?

Question

Using a combination of recording and finding tricks online, I put together code to run when a user opens their workbook. The primary goal is to format every 11 columns starting with column 5 (using indicators).

It works for everyone except one user, who receives the following error: "Run-time error: '1004' Select method of Worksheet class failed". According to my research, it seems like avoiding "Select" might help, but I'm a bit lost on converting what I have and could use some guidance.

If any other opportunities for improvement, I'd also welcome it. Thanks!

Here's what I'm doing now:

Private Sub Workbook_Open()

    Application.ScreenUpdating = False

    Sheets("Sheets1").Select

    For i = 5 To 1000 Step 11
      Range(Cells(13,i),Cells(1000,i).Select
      Selection.ColumnWidth = 2
      Selection.FormatConditions.AddIconSetCondition
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1)
         .ReverseOrder = False
         .ShowIconOnly = False
         .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
      End With
      Selection.FormatConditions(1).IconCriteria(1).Icon = xlIconYellowExclamation
      With Selection.FormatConditions(1).IconCriteria(2)
         .Type = xlConditionValueNumber
         .Value = 1
         .Operator = 7
         .Icon = xlIconGreenCheck
      End With
      With Selection.FormatConditions(1).IconCriteria(3)
         .Type = xlConditionValueNumber
         .Value = 2
         .Operator = 7
         .Icon = xlIconRedCross
      End With

      Next i

      Columns("A:A").ColumnWidth = 50
      Columns("B:B").Select
      ActiveWindow.FreezePanes = True

      Application.ScreenUpdating = True

End Sub

How do I convert my code to not use "Select"?

0 Answers0