How Can I Streamline/Modularize This Repetitive Code?

[CaliKidd]

I have a userform (called Format_Worksheet) that has a combobox control (called Data_Header_Backcolors) that allows the user to make selections that change the background colors of cells in a worksheet (called Dashboard). Currently, the following subroutine handles this, as follows:

Public Sub Dashboard_Data_Header_Backcolors()
    Select Case Format_Worksheet.Data_Header_Backcolors
        Case "White/White"
            With Dashboard
                .Range("A2:B5,E2:E5").Select
                With Selection.Interior
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = 0
                End With
                .Range("C2:D5,F2:H5").Select
                With Selection.Interior
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = 0
                End With
                .Range("A1").Select
            End With
        Case "White/Grey"
            With Dashboard
                .Range("A2:B5,E2:E5").Select
                With Selection.Interior
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = 0
                End With
                .Range("C2:D5,F2:H5").Select
                With Selection.Interior
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.249977111117893
                End With
                .Range("A1").Select
            End With
...etc...
   End Select
End Sub

While this sub works ok, I recognize there is a lot of redundant code, which makes for a maintenance headache and is making my file larger than it needs to be.

What I would like to do is to pass the .ThemeColor and .TintAndShade values to a subroutine since those are the only things that change in each "case". Please note, however, that for each case there are two different ranges I am colorizing (Range("A2:B5,E2:E5") and Range("C2:D5,F2:H5")), which each have different .ThemeColor and .TintAndShade values.

I've read some books that mention the ability to pass values into a sub as arguments, but I'm not exactly sure how to do it. I'm not quite there yet in my skill level.

Can an expert please lend a hand?

 

[Leith Ross]

Hello CaliKidd,

Just expand the cases in the Select Case statement in the macro below.

Public Sub Dashboard_Data_Header_Backcolors()

    Dim TC1 As Double, TC2 As Double
    Dim TS1 As Double, TS2 As Double
    
    If Format_Worksheet.Data_Header_BackColors.ListIndex > -1 Then
        Select Case Format_Worksheet.Data_Header_BackColors
            Case "White/White"
                TC1 = xlThemeColorDark1
                TS1 = 0
                
                TC2 = xlThemeColorDark1
                TS2 = 0
            Case "White/Grey"
                TC1 = xlThemeColorDark1
                TS1 = 0
                    
                TC2 = xlThemeColorDark1
                TS2 = -0.249977111117893
        End Select
        
        With Dashboard.Range("A2:B5,E2:E5").Interior
            .ThemeColor = TC1
            .TintAndShade = TS1
        End With
        
        With Dashboard.Range("C2:D5,F2:H5").Interior
            .ThemeColor = TC2
            .TintAndShade = TS2
        End With
    End If
   
End Sub

 

[CaliKidd]

Hi Leith,

No wonder my computer doesn't like me... I see now I am making it work too hard, lol.

Thanks for the help. It reduced the size of the sub substantially.

CK

 

[Leith Ross]

Hello CaliKidd,

Glad I could help out.