+ Reply to Thread
Results 1 to 3 of 3

Clean up code

Hybrid View

madhatter40 Clean up code 10-11-2014, 08:20 AM
apo Re: Clean up code 10-11-2014, 09:09 AM
TMS Re: Clean up code 10-11-2014, 09:21 AM
  1. 10-11-2014, 08:20 AM #1
    madhatter40 is offline
    Forum Contributor
    Join Date
    12-14-2012
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    143

    Clean up code

    I am looking to see if any of the following code could be cleaned up? Any help would be greatful
    Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("G2")) Is Nothing Then Exit Sub

'Application.EnableEvents = False

        Dim White_Cells As String
        Dim Green_Cells As String
        Dim Gray_Cells As String
        Dim Other_Value As String
        Dim Focus_Cell As String
        Dim MC As String
        Dim Disp As String
        Dim CandL As String
        Dim Labels As String
        Dim Disk As String
        Dim cal As String
        Dim calc As String
        Dim scrap_total As String
        Dim Filter_paper As String
        Dim machine As String
        
    Select Case Range("G2")
    
           

        Case "PL034", "PL037"
        
                ' This code selects clears them and resets the cursor to B5
                Range("a8,b5,C5,B8,C8,G8:H12").Select
                Selection.ClearContents
        
                Range("G8").Value = "Master Cases"
                Range("G9").Value = "Dispensers"
                Range("G10").Value = "Cups/Lids"
                Range("G11").Value = "Labels"
                Range("G12").Value = ""
                Range("B8").Value = "=D5*8"
                Range("D8").Value = "=B8-C8"
                Range("E8").Value = "=D8*0.18575"
                Range("A8").Value = "FP 320"
                Range("B7").Value = "Total # of Cups Made"
                
            'This selects the cells to color Green
                Range("B5,C5,C8,H8:H11").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 52377
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
            ' This turns the cells White
    
                Range("D5,B8").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
            ' This selects the cells to turn Gray
                 Range("G8:G12,H12").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = -0.249977111117893
                        .PatternTintAndShade = 0
                    End With
                    
                Range("B5").Select
        
        Case "PL124 Short Filter", "PL125 Short Filter", "PL126 Short Filter", "PL127 Short Filter"
        
                Range("G8").Value = "Master Cases"
                Range("G9").Value = "Dispensers"
                Range("G10").Value = "Cups/Lids"
                Range("G11").Value = "Labels"
                Range("G12").Value = ""
                Range("B8").Value = "=D5*8"
                Range("D8").Value = "=B8-C8"
                Range("E8").Value = "=D8*0.25"
                Range("A8").Value = "FP 400 Short Filter"
                Range("B7").Value = "Total # of Cups Made"
                
            'This selects the cells to color Green
                Range("B5,C5,C8,H8:H11").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 52377
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
            ' This turns the cells White
    
                Range("D5,B8").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                ' This selects the cells to turn Gray
                Range("G8:G12,H12").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = -0.249977111117893
                        .PatternTintAndShade = 0
                    End With
                    
                Range("B5").Select
        
        Case "PL124 Long Filter", "PL125 Long Filter", "PL126 Long Filter", "PL127 Long Filter"
        
                Range("G8").Value = "Master Cases"
                Range("G9").Value = "Dispensers"
                Range("G10").Value = "Cups/Lids"
                Range("G11").Value = "Labels"
                Range("G12").Value = "Disk"
                Range("B8").Value = "=D5*8"
                Range("D8").Value = "=B8-C8"
                Range("E8").Value = "=D8*0.31"
                Range("A8").Value = "FP 400 Long Filter"
                Range("B7").Value = "Total # of Cups Made"
                
            'This selects the cells to color Green
                Range("B5,C5,C8,H8:H12").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 52377
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
            ' This turns the cells White
    
                Range("D5,B8").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                ' This selects the cells to turn Gray
                Range("G8:G12").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = -0.249977111117893
                        .PatternTintAndShade = 0
                    End With
                    
                Range("B5").Select
            
               
        Case "PL118", "PL123"
        
                Range("G8").Value = "Club Cases"
                Range("G9").Value = "Cups/Lids"
                Range("G10").Value = ""
                Range("G11").Value = ""
                Range("G12").Value = ""
                Range("B8").Value = "=D5*16"
                Range("D8").Value = "=B8-C8"
                Range("E8").Value = "=D8*0.18575"
                Range("A8").Value = "FP 700"
                Range("B7").Value = "Total # of Cups Made"
                
            'This selects the cells to color Green
                Range("B5,C5,C8,H8:H9").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 52377
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
            ' This turns the cells White
    
                Range("D5,B8").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                ' This selects the cells to turn Gray
                Range("G8:G12,H10:H12").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = -0.249977111117893
                        .PatternTintAndShade = 0
                    End With
                    
                Range("B5").Select
                       
                  
        Case "PL108", "PL116"
        
                Range("G8").Value = "Master Cases"
                Range("G9").Value = "Dispensers"
                Range("G10").Value = "Cups/Lids"
                Range("G11").Value = ""
                Range("G12").Value = ""
                Range("B8").Value = "=D5*12"
                Range("D8").Value = "=B8-C8"
                Range("E8").Value = ""
                Range("A8").Value = "Optima 720"
                Range("B7").Value = "Total # of Cups Made"
                
            'This selects the cells to color Green
                Range("B5,C5,C8,H8:H10").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 52377
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
            ' This turns the cells White
    
                Range("D5,B8").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                ' This selects the cells to turn Gray
                Range("G8:G12,H11:H12").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = -0.249977111117893
                        .PatternTintAndShade = 0
                    End With
                    
                Range("B5").Select
            
            
         Case "PL058 Short Filter", "PL083 Short Filter", "PL079 Short Filter"
        
                Range("G8").Value = "Master Cases"
                Range("G9").Value = "Dispensers"
                Range("G10").Value = "Cups/Lids"
                Range("G11").Value = ""
                Range("G12").Value = ""
                Range("B8").Value = ""
                Range("D8").Value = "=B8-C8"
                Range("E8").Value = "=D8*0.0909"
                Range("A8").Value = "OPEM 400"
                Range("B7").Value = "Total# of Cups Requested"
                
            'This selects the cells to color Green
            
                Range("B8,C8,H8:H10").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 52377
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
            ' This turns the cells White
    
                Range("D5").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                ' This selects the cells to turn Gray
                Range("B5,C5,G8:G12,H11:H12").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = -0.249977111117893
                        .PatternTintAndShade = 0
                    End With
                    
                Range("B8").Select
                        
           
         Case "PL058 Long Filter", "PL083 Long Filter", "PL079 Long Filter"
        
       
                Range("G8").Value = "Master Cases"
                Range("G9").Value = "Dispensers"
                Range("G10").Value = "Cups/Lids"
                Range("G11").Value = "Labels"
                Range("G12").Value = "Disk"
                Range("B8").Value = ""
                Range("D8").Value = "=B8-C8"
                Range("E8").Value = "=D8*0.1133"
                Range("A8").Value = "OPEM 400"
                Range("B7").Value = "Total# of Cups Requested"
                
            'This selects the cells to color Green
            
                Range("B8,C8,H8:H12").Select
                   With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 52377
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
            ' This turns the cells White
    
                Range("D5").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
                ' This selects the cells to turn Gray
                Range("B5,C5,G8:G12").Select
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark1
                        .TintAndShade = -0.249977111117893
                        .PatternTintAndShade = 0
                    End With
                    
                Range("B8").Select
                
           
    End Select

'Application.EnableEvents = True

End Sub
    Reply With QuoteRegister To Reply
  2. 10-11-2014, 09:09 AM #2
    apo is offline
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Clean up code

    Perhaps post a sample Workbook and a detailed explanation of what you want to do..

    There's many ways your code coudl be cleaned up..

    Example:
     Range("G8").Value = "Master Cases"
                Range("G9").Value = "Dispensers"
                Range("G10").Value = "Cups/Lids"
                Range("G11").Value = "Labels"
                Range("G12").Value = "Disk"

    Could be :
    Range("G8).Resize(5).Value = Array("Master Cases", "Dispensers", "Cups/Lids", "Labels","Disk")
    But, regardless.. a Workbook attached an clear explanation will serve your needs better..
    Reply With QuoteRegister To Reply
  3. 10-11-2014, 09:21 AM #3
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,089

    Re: Clean up code

    Another common example:

        Range("D5,B8").Select
        With Selection.Interior

    can be written:

        With Range("D5,B8").Interior

    That will remove the need to select cells, etc.

    Given that a lot of the code is repetitive, you would do better to create a subroutine that executes the common code. Sometimes, only one parameter varies and, in that case, it could be passed to the subroutine.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Clean up my code!
    By Mr ZN in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2013, 12:19 AM
  2. Code clean up
    By zplugger in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-07-2012, 07:17 PM
  3. [SOLVED] code clean up
    By promoboy2 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-03-2012, 06:06 PM
  4. [SOLVED] VBA Code to optimize and clean data- clean out numerical/ or symbol
    By tracylsr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-05-2012, 04:21 PM
  5. VBA code clean up help
    By recon427 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-17-2012, 01:19 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1