Speed up 'brute force' macro

Viewed 25

Trying to speed up this macro, it just loops through 'C-L" columns and changes the format based on adjacent cell values.

Sub finalfinal3()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Sheets("FBM").Activate
    
   Range("C4").Select
    If ActiveCell.Offset(-1, 0) = "Grand Total" Then
        ActiveCell.Offset(1, 0).Select
    Else
    
     Do Until ActiveCell.row = 500
    
       If ActiveCell.Offset(0, -1).Value = "OnOrder" Then
       ActiveCell.ClearFormats
       ActiveCell.Font.Color = RGB(255, 255, 255)
       
            ElseIf ActiveCell.Offset(0, -1).Value = "OnHand" Then
            ActiveCell.ClearFormats
            ActiveCell.Font.Color = RGB(0, 0, 0)
        
                Else
                ActiveCell.ClearFormats
                ActiveCell.Font.Color = RGB(217, 217, 217)

        End If
        
        ActiveCell.Offset(1, 0).Select
        
    Loop
    
  End If
      
    
     Range("D4").Select
      If ActiveCell.Offset(-1, 0) = "Grand Total" Then
        ActiveCell.Offset(1, 0).Select
      Else
      
       Do Until ActiveCell.row = 500
        
        If ActiveCell.Offset(0, -2).Value = "OnOrder" Then
        ActiveCell.ClearFormats
        ActiveCell.Font.Color = RGB(255, 255, 255)
        
            ElseIf ActiveCell.Offset(0, -2).Value = "OnHand" Then
            ActiveCell.ClearFormats
            ActiveCell.Font.Color = RGB(0, 0, 0)
                
                Else
                ActiveCell.ClearFormats
                ActiveCell.Font.Color = RGB(217, 217, 217)

      End If
        ActiveCell.Offset(1, 0).Select
    Loop
       
      End If
      
   Range("E4").Select
      If ActiveCell.Offset(-1, 0) = "Grand Total" Then
 ActiveCell.Offset(1, 0).Select
    Else
    Do Until ActiveCell.row = 500
   
     If ActiveCell.Offset(0, -3).Value = "OnOrder" Then
     ActiveCell.ClearFormats
      ActiveCell.Font.Color = RGB(255, 255, 255)
      ElseIf ActiveCell.Offset(0, -3).Value = "OnHand" Then
      ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(0, 0, 0)
     Else
     ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(217, 217, 217)

      End If
        ActiveCell.Offset(1, 0).Select
    Loop
    
        
      End If
      
   Range("F4").Select
      If ActiveCell.Offset(-1, 0) = "Grand Total" Then
 ActiveCell.Offset(1, 0).Select
    Else
    Do Until ActiveCell.row = 500
   
   If ActiveCell.Offset(0, -4).Value = "OnOrder" Then
   ActiveCell.ClearFormats
      ActiveCell.Font.Color = RGB(255, 255, 255)
      ElseIf ActiveCell.Offset(0, -4).Value = "OnHand" Then
      ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(0, 0, 0)
     Else
     ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(217, 217, 217)
      End If
        ActiveCell.Offset(1, 0).Select
    Loop
        
      End If
      
Range("G4").Select
      If ActiveCell.Offset(-1, 0) = "Grand Total" Then
 ActiveCell.Offset(1, 0).Select
    Else
    Do Until ActiveCell.row = 500
   
   If ActiveCell.Offset(0, -5).Value = "OnOrder" Then
   ActiveCell.ClearFormats
      ActiveCell.Font.Color = RGB(255, 255, 255)
      ElseIf ActiveCell.Offset(0, -5).Value = "OnHand" Then
      ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(0, 0, 0)
     Else
     ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(217, 217, 217)

      End If
        ActiveCell.Offset(1, 0).Select
    Loop
    
      End If
      
     Range("H4").Select
       If ActiveCell.Offset(-1, 0) = "Grand Total" Then
 ActiveCell.Offset(1, 0).Select
    Else
    Do Until ActiveCell.row = 500
   
   If ActiveCell.Offset(0, -6).Value = "OnOrder" Then
   ActiveCell.ClearFormats
      ActiveCell.Font.Color = RGB(255, 255, 255)
      ElseIf ActiveCell.Offset(0, -6).Value = "OnHand" Then
      ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(0, 0, 0)
     Else
     ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(217, 217, 217)
      End If
        ActiveCell.Offset(1, 0).Select
    Loop
    
      End If
      
      Range("I4").Select
        If ActiveCell.Offset(-1, 0) = "Grand Total" Then
 ActiveCell.Offset(1, 0).Select
    Else
    Do Until ActiveCell.row = 500
   
   If ActiveCell.Offset(0, -7).Value = "OnOrder" Then
   ActiveCell.ClearFormats
      ActiveCell.Font.Color = RGB(255, 255, 255)
      ElseIf ActiveCell.Offset(0, -7).Value = "OnHand" Then
      ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(0, 0, 0)
     Else
     ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(217, 217, 217)

      End If
        ActiveCell.Offset(1, 0).Select
    Loop
    
      End If
      
     Range("J4").Select
        If ActiveCell.Offset(-1, 0) = "Grand Total" Then
 ActiveCell.Offset(1, 0).Select
    Else
    Do Until ActiveCell.row = 500
   
   If ActiveCell.Offset(0, -8).Value = "OnOrder" Then
   ActiveCell.ClearFormats
      ActiveCell.Font.Color = RGB(255, 255, 255)
      ElseIf ActiveCell.Offset(0, -8).Value = "OnHand" Then
      ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(0, 0, 0)
     Else
     ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(217, 217, 217)

      End If
        ActiveCell.Offset(1, 0).Select
    Loop
    
      End If
      
      Range("K4").Select
        If ActiveCell.Offset(-1, 0) = "Grand Total" Then
 ActiveCell.Offset(1, 0).Select
    Else
    Do Until ActiveCell.row = 500
   
   If ActiveCell.Offset(0, -9).Value = "OnOrder" Then
   ActiveCell.ClearFormats
      ActiveCell.Font.Color = RGB(255, 255, 255)
      ElseIf ActiveCell.Offset(0, -9).Value = "OnHand" Then
      ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(0, 0, 0)
     Else
     ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(217, 217, 217)

      End If
        ActiveCell.Offset(1, 0).Select
    Loop
    
      End If
      
          Range("l4").Select
      If ActiveCell.Offset(-1, 0) = "Grand Total" Then
 ActiveCell.Offset(1, 0).Select
    Else
    Do Until ActiveCell.row = 500
   
   If ActiveCell.Offset(0, -10).Value = "OnOrder" Then
   ActiveCell.ClearFormats
      ActiveCell.Font.Color = RGB(255, 255, 255)
      ElseIf ActiveCell.Offset(0, -10).Value = "OnHand" Then
      ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(0, 0, 0)
     Else
     ActiveCell.ClearFormats
     ActiveCell.Font.Color = RGB(217, 217, 217)

      End If
        ActiveCell.Offset(1, 0).Select
    Loop
    
      End If
      
  End Sub
0 Answers
Related