wanting to change from command buttons on my spreadsheet to a combobox... need some help with the code

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
476
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
To help explain what the spreadsheet does, here is a helpful pic. :)

Default view has names across the top, procedures down the side, and my command buttons in the top left corner that, when clicked filters the employees down to only the employees working in that department (actually, it doesnt FILTER at all... I have a hidden row below the name that has the wild card abbreviation for that department that the code looks for, then hides every column that doesnt contain that wild card and unhides the ones that do.)

The default view when the spreadsheet is opened:

2eykmbq.jpg



Here is the view after clicking on the "Customer Service" button (command button(14) in the code posted below.)
(shows only the CSR employees and the SOPs that are applicable to that department... (other SOPs are still there, I just have them formatted so that they are not as visible.)

wmilb9.jpg




So what I need to do, is have a combobox that contains all 19 departments (the colored buttons in the top left hand corner) and runs the existing code like I have attached (or modified code, I mean... whatever I need to do to it to make it work with a combobox selection) so that it sorts it and performs all the other functions just like it does wiht the command buttons. What will be the easiest way/method to do this?? Thanks!


Code:
Private Sub CommandButton14_Click()


    ' button for CSR DEPT (Cs)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Cs*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Cs*" Then
.Interior.Color = 12648384
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin




For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Coleman, Hillary" Then Cell.Interior.Color = 12648384
If Cell = " Coleman, Hillary " Then Cell.Font.Bold = True


Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton14.Font.Bold = True
CommandButton14.Font.Italic = True
CommandButton14.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

Thanks for any help or assistance!! :)
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
For those curious on how this all works, the button that says "Show Training Date" unhides the column adjacent to the column with the name and then that unhidden column shows the date that the training for each SOP (if applicable) was completed.

rro7sw.jpg


Below is a screen shot showing all the rows and columns unhidden:

Row 3 contains the wildcard abbreviation for the department that each employee works in (they can also be listed in mulitple departments in order to show all the employees that are 'cross trained' in multiple departments)

Column A has wild card abbreviations for departments also, but these are used to filter which each SOP (procedure) is to be shown (and color coded to match the color of the command button) for the corresponding department.


6gh7b8.jpg
 
Upvote 0
Here is all my code for the spreadsheet (if this is more helpful):

THIS WORKBOOK:

Code:
Private Sub Workbook_Open()
Worksheets("Current").Activate


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False




ActiveWindow.ScrollColumn = 1


    Application.DisplayFullScreen = True
    ActiveWindow.DisplayHeadings = False
    
Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
    
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 2), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 2), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 2), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 2), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    
    With Columns("B")
      .Hidden = False
    End With
    
    With Columns("E")
      .Hidden = True
    End With
    
    With Columns("C")
    .Hidden = False
    End With


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Dim i As Long
 
For i = 3 To ActiveSheet.UsedRange.Columns.Count Step 2
  Columns(i).Hidden = Not Columns(i).Hidden
Next i


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
    
Private Sub Workbook_Close()


Application.DisplayFullScreen = False
ActiveWindow.DisplayHeadings = True


End Sub


SHEET1



Code:
  ' NOTE: Current count for Column is 258 and IX


Private Sub CommandButton17_Click()


    ' button for RESET ALL


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False




Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
    For Each Cell In Range(Cells(4, 6), Cells(4, lCol))


If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    
    With Columns("B")
      .Hidden = False
    End With


    With Columns("C")
      .Hidden = False
    End With
    
    With Columns("A")
      .Hidden = True
    End With
    
Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2: B4").Font.Color = &HC000&
Range("B2: B4").Font.Bold = True
Range("B2: B4").Borders.Weight = xlThin


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False






Dim i As Long
 
For i = 3 To ActiveSheet.UsedRange.Columns.Count Step 2
  Columns(i).Hidden = Not Columns(i).Hidden
Next i


Application.Goto Reference:=Range("b1"), Scroll:=True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton18_Click()


    ' button for SHOW DATES of Training
    
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    For Each Cell In Range(Cells(6, 6), Cells(rCol, lCol))
    
If Cell.Value = "X" And Columns(Cell.Column).Hidden = False Then Columns(Cell.Column + 1).Hidden = False
Next Cell




Range("B2: B4").Font.Color = &HC000&
Range("B2: B4").Font.Bold = True
Range("B2: B4").Borders.Weight = xlThin


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton24_Click()


    ' button for SHOWING DEPARTMENT ROWS


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


    With Rows("2:4")
      .Hidden = Not .Hidden
    End With


Range("B2: B4").Font.Color = &HC000&
Range("B2: B4").Font.Bold = True
Range("B2: B4").Borders.Weight = xlThin


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub




Private Sub CommandButton20_Click()


    ' button for SHOW ALL
    
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = False
Next




Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2: B4").Font.Color = &HC000&
Range("B2: B4").Font.Bold = True
Range("B2: B4").Borders.Weight = xlThin


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


Application.Goto Reference:=Range("a1"), Scroll:=True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton26_Click()


    ' button for 24mo REVIEW FOR SOPs


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


    With Columns("C")
      .Hidden = Not .Hidden
    End With


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub




Private Sub CommandButton21_Click()


    ' button for HOURLY
    
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    For Each Cell In Range(Cells(2, 6), Cells(2, lCol))
If Cell = "Hr" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True
Next


Range(Cells(1, 2), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 2), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 2), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 2), Cells(rCol, lCol)).Borders.Weight = xlThin


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub




Private Sub CommandButton22_Click()


    ' button for SALARY
    
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    For Each Cell In Range(Cells(2, 6), Cells(2, lCol))
If Cell = "Sa" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True
Next


Range(Cells(1, 2), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 2), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 2), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 2), Cells(rCol, lCol)).Borders.Weight = xlThin


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton1_Click()


    ' button for PACKAGING (Pr)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))


If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("C")
      .Hidden = True
    End With
    With Columns("A")
      .Hidden = True
    End With
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Pr*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Pr*" Then
                .Interior.Color = 65280
                .Font.Bold = True
                .Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
Next


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Dockery, Tom" Then Cell.Interior.Color = 65280
If Cell = "Dockery, Tom" Then Cell.Font.Bold = True
If Cell = "Dockery, Tom" Then Cell.Interior.Color = 65280
If Cell = "Dockery, Tom" Then Cell.Font.Bold = True


If Cell = "Dockery, Jim" Then Cell.Interior.Color = 65280
If Cell = "Dockery, Jim" Then Cell.Font.Bold = True
If Cell = "Dockery, Jim" Then Cell.Interior.Color = 65280
If Cell = "Dockery, Jim" Then Cell.Font.Bold = True
Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton1.Font.Bold = True
CommandButton1.Font.Italic = True
CommandButton1.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
Private Sub CommandButton19_Click()


    ' button for PACKAGING-JIM (PrJM)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("C")
      .Hidden = True
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*PrJM*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Pr*" Then
                .Interior.Color = 65280
                .Font.Bold = True
                .Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Dockery, Jim" Then Cell.Interior.Color = 65280
If Cell = "Dockery, Jim" Then Cell.Font.Bold = True
If Cell = "Dockery, Jim" Then Cell.Interior.Color = 65280
If Cell = "Dockery, Jim" Then Cell.Font.Bold = True
Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton19.Font.Bold = True
CommandButton19.Font.Italic = True
CommandButton19.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
Private Sub CommandButton23_Click()


    ' button for PACKAGING-TOM (PrTM)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("C")
      .Hidden = True
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*PrTM*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Pr*" Then
                .Interior.Color = 65280
                .Font.Bold = True
                .Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Dockery, Tom" Then Cell.Interior.Color = 65280
If Cell = "Dockery, Tom" Then Cell.Font.Bold = True
If Cell = "Dockery, Tom" Then Cell.Interior.Color = 65280
If Cell = "Dockery, Tom" Then Cell.Font.Bold = True


Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton23.Font.Bold = True
CommandButton23.Font.Italic = True
CommandButton23.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
Private Sub CommandButton2_Click()


    ' button for BLENDING (Bl)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("C")
      .Hidden = True
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Bl*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Bl*" Then
                .Interior.Color = 8421631
                .Font.Bold = True
                .Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Thurmond, David" Then Cell.Interior.Color = 65280
If Cell = "Thurmond, David" Then Cell.Font.Bold = True
If Cell = "Thurmond, David" Then Cell.Interior.Color = 65280
If Cell = "Thurmond, David" Then Cell.Font.Bold = True
  Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton2.Font.Bold = True
CommandButton2.Font.Italic = True
CommandButton2.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton3_Click()


    ' button for POWDERS (Powders)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Po*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Po*" Then
.Interior.Color = 12648447
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next
  
Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Cavnar, Eddie" Then Cell.Interior.Color = 12648447
If Cell = "Cavnar, Eddie" Then Cell.Font.Bold = True
Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton3.Font.Bold = True
CommandButton3.Font.Italic = True
CommandButton3.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton4_Click()


    ' button for BUILDING 8


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*B8*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*B8*" Then
.Interior.Color = 13382655
.Font.Color = 6750207
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin




For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Mura, Mark" Then Cell.Interior.Color = 13382655
If Cell = "Mura, Mark" Then Cell.Font.Color = 6750207
If Cell = "Mura, Mark" Then Cell.Font.Bold = True


Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton4.Font.Bold = True
CommandButton4.Font.Italic = True
CommandButton4.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton5_Click()


    ' button for OIL FIELD (OilField)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Of*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Of*" Then
.Interior.Color = 16153216
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin
For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Klemstein, Chantz" Then Cell.Interior.Color = 16153216
If Cell = "Klemstein, Chantz" Then Cell.Font.Bold = True
If Cell = "Klemstein, Chantz" Then Cell.Font.Color = &HFFFFFF
Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton5.Font.Bold = True
CommandButton5.Font.Italic = True
CommandButton5.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
Private Sub CommandButton25_Click()


    ' button for OIL FIELD - Flaker


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*OfFL*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*OfFL*" Then
.Interior.Color = 16153216
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Klemstein, Chantz" Then Cell.Interior.Color = 16153216
If Cell = "Klemstein, Chantz" Then Cell.Font.Bold = True
If Cell = "Klemstein, Chantz" Then Cell.Font.Color = &HFFFFFF
Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton25.Font.Bold = True
CommandButton25.Font.Italic = True
CommandButton25.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton6_Click()


    ' button for Labels department (Labels)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*La*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*La*" Then
.Interior.Color = 16776960
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Smith, Michelle" Then Cell.Interior.Color = 16776960
If Cell = "Smith, Michelle" Then Cell.Font.Bold = True
Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton6.Font.Bold = True
CommandButton6.Font.Italic = True
CommandButton6.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton7_Click()


    ' button for Shipping department (SH)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Sh*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Sh*" Then
.Interior.Color = 65535
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin




For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Baker, Pieter" Then Cell.Interior.Color = 65535
If Cell = "Baker, Pieter" Then Cell.Font.Bold = True
Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton7.Font.Bold = True
CommandButton7.Font.Italic = True
CommandButton7.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton8_Click()


    ' button for Maintenance department (Ma)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Ma*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Ma*" Then
.Interior.Color = 39423
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Knobloch, Eric" Then Cell.Interior.Color = 39423
If Cell = "Knobloch, Eric" Then Cell.Font.Bold = True
Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton8.Font.Bold = True
CommandButton8.Font.Italic = True
CommandButton8.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton9_Click()


    ' button for LAB (QC)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*QC*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*QC*" Then
.Interior.Color = 12632055
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin




For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Bowhay, Chris" Then Cell.Interior.Color = 12632055
If Cell = "Bowhay, Chris" Then Cell.Font.Bold = True
Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton9.Font.Bold = True
CommandButton9.Font.Italic = True
CommandButton9.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
Private Sub CommandButton10_Click()


' button for Supervisors (Su)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Su*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Su*" Then
.Interior.Color = 16711680
.Font.Color = 65535
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Nowak, Richard" Then Cell.Interior.Color = 16711680
If Cell = "Nowak, Richard" Then Cell.Font.Color = 65535
If Cell = "Nowak, Richard" Then Cell.Font.Bold = True


Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton10.Font.Bold = True
CommandButton10.Font.Italic = True
CommandButton10.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub


Private Sub CommandButton11_Click()


    ' button for SALES DEPT (Sa)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Sa*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Sa*" Then
.Interior.Color = 1993782
.Font.Color = &H80C0FF
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Aimes, Kelly" Then Cell.Interior.Color = 1993782
If Cell = "Aimes, Kelly" Then Cell.Font.Color = &H80C0FF
If Cell = "Aimes, Kelly" Then Cell.Font.Bold = True


Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton11.Font.Bold = True
CommandButton11.Font.Italic = True
CommandButton11.Font.Underline = True




Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
Private Sub CommandButton12_Click()


    ' button for PURCHASING DEPT (Pu)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Pu*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Pu*" Then
.Interior.Color = 12632256
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Dockery, Jim" Then Cell.Interior.Color = 12632256
If Cell = "Dockery, Jim" Then Cell.Font.Bold = True
Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton12.Font.Bold = True
CommandButton12.Font.Italic = True
CommandButton12.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
Private Sub CommandButton13_Click()


    ' button for ACCOUNTING DEPT (Ac)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Ac*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Ac*" Then
.Interior.Color = 16761087
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Nattier, Jim" Then Cell.Interior.Color = 16761087
If Cell = "Nattier, Jim" Then Cell.Font.Bold = True
Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton13.Font.Bold = True
CommandButton13.Font.Italic = True
CommandButton13.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
Private Sub CommandButton14_Click()


    ' button for CSR DEPT (Cs)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Cs*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Cs*" Then
.Interior.Color = 12648384
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin




For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Coleman, Hillary" Then Cell.Interior.Color = 12648384
If Cell = " Coleman, Hillary " Then Cell.Font.Bold = True


Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton14.Font.Bold = True
CommandButton14.Font.Italic = True
CommandButton14.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
Private Sub CommandButton15_Click()


' button for QC & MGT DEPT (Mg)


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Mg*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Mg*" Then
.Interior.Color = &H5590
.Font.Color = &HFFC0FF
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton15.Font.Bold = True
CommandButton15.Font.Italic = True
CommandButton15.Font.Underline = True


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
Private Sub CommandButton16_Click()


' button for HUMAN RESOURCES  (Hr) &H8000&


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
For Each Cell In Range(Cells(4, 6), Cells(4, lCol))
If Cell = "a" Then Columns(Cell.Column).Hidden = True Else Columns(Cell.Column).Hidden = False
Next


Range(Cells(1, 1), Cells(rCol, lCol)).Interior.Color = xlNone
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Color = vbBlack
Range(Cells(1, 1), Cells(rCol, lCol)).Font.Bold = False
Range(Cells(1, 1), Cells(rCol, lCol)).Borders.Weight = xlThin
Range(Cells(5, 1), Cells(rCol, lCol)).RowHeight = 16
Range(Cells(9, 3), Cells(rCol, 3)).Font.Color = vbWhite


    With Rows("2:4")
      .Hidden = True
    End With
    With Columns("B")
      .Hidden = False
    End With
    With Columns("A")
      .Hidden = True
    End With
    
    For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Hr*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Hr*" Then
.Interior.Color = &H8000&
.Font.Color = &HFFFFFF
.Font.Bold = True
.Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
  Next


Range("B5").Font.Color = &HFF&
Range("B5").Font.Bold = True
Range("B5").Borders.Weight = xlMedium


Range("B2:B4").Font.Color = &HC000&
Range("B2:B4").Font.Bold = True
Range("B2:B4").Borders.Weight = xlThin


For Each Cell In Range(Cells(1, 5), Cells(1, lCol))
If Cell = "Nattier, Jim" Then Cell.Interior.Color = &H8000&
If Cell = "Nattier, Jim" Then Cell.Font.Color = &HFFFFFF
If Cell = "Nattier, Jim" Then Cell.Font.Bold = True


Next


CommandButton1.Font.Bold = False
CommandButton1.Font.Italic = False
CommandButton1.Font.Underline = False
CommandButton2.Font.Bold = False
CommandButton2.Font.Italic = False
CommandButton2.Font.Underline = False
CommandButton3.Font.Bold = False
CommandButton3.Font.Italic = False
CommandButton3.Font.Underline = False
CommandButton4.Font.Bold = False
CommandButton4.Font.Italic = False
CommandButton4.Font.Underline = False
CommandButton5.Font.Bold = False
CommandButton5.Font.Italic = False
CommandButton5.Font.Underline = False
CommandButton6.Font.Bold = False
CommandButton6.Font.Italic = False
CommandButton6.Font.Underline = False
CommandButton7.Font.Bold = False
CommandButton7.Font.Italic = False
CommandButton7.Font.Underline = False
CommandButton8.Font.Bold = False
CommandButton8.Font.Italic = False
CommandButton8.Font.Underline = False
CommandButton9.Font.Bold = False
CommandButton9.Font.Italic = False
CommandButton9.Font.Underline = False
CommandButton10.Font.Bold = False
CommandButton10.Font.Italic = False
CommandButton10.Font.Underline = False
CommandButton11.Font.Bold = False
CommandButton11.Font.Italic = False
CommandButton11.Font.Underline = False
CommandButton12.Font.Bold = False
CommandButton12.Font.Italic = False
CommandButton12.Font.Underline = False
CommandButton13.Font.Bold = False
CommandButton13.Font.Italic = False
CommandButton13.Font.Underline = False
CommandButton14.Font.Bold = False
CommandButton14.Font.Italic = False
CommandButton14.Font.Underline = False
CommandButton15.Font.Bold = False
CommandButton15.Font.Italic = False
CommandButton15.Font.Underline = False
CommandButton16.Font.Bold = False
CommandButton16.Font.Italic = False
CommandButton16.Font.Underline = False
CommandButton19.Font.Bold = False
CommandButton19.Font.Italic = False
CommandButton19.Font.Underline = False
CommandButton23.Font.Bold = False
CommandButton23.Font.Italic = False
CommandButton23.Font.Underline = False


CommandButton16.Font.Bold = True
CommandButton16.Font.Italic = True
CommandButton16.Font.Underline = True




Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
 
Upvote 0
I've googled this and worked on it some last night, but cannot figure out how the best way to do this. Now I'm thinking that I could run a macro based on the combobox selection and have that macro perform the function of the command button(?) Would that work?? Thanks
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top