VBA - Change colour of cell based on checkbox

[Inexperienced]

Hi all,

I'm working with a frankenstein VBA code left behind from the companies previous engineer. I am NEW to VBA and whilst trying to remove out of date code - I wanted to keep one section.
Essentially - this function changed the COLOUR of cells based on whether or not employees had selected "enable macros" (The yellow tab that appears when you first open the document).
(To specify all other Modules are working fine with my changes - this was the sub inside of the sheet 1)

Here is the code he worked with:

'Change la zone rouge en verte
If Feuil1.CheckBox1 = False Then Range("CI5:DC20").Interior.ColorIndex = 4 'CONTROL TEST

The cells have not changed nor the sheet (yes its also Quebec French, but changes from red to green in sheet 1 cells)
I am just not sure how to start/end this?

Any help would be great!

 

[Micron]

Not enough info to be able to help much and your question is kinda vague. Start and end what?
That changes the interior to red if the checkbox is not checked. What we can't tell is what event that code is in. Perhaps explain what's happening and what you want to have happen and when. Perhaps if you posted the rest of that procedure we can tell what event it belongs to. If you post code, please paste between code tags (use vba button on posting toolbar) to maintain indentation and readability.

 

[davesexcel]

Are these on the same sheet?

 

[Inexperienced]

Sorry - like I said very new to VBA and don't have a great understanding on most of it.

This was the FULL original code (listed in Sheet1 - not a module)
All of the Data in RED is no longer needed.
And by start and end I mean - "Private SUB_ Workbook change/End if End Sub - leaving the current with removing the data in red - does not work.
Not sure what would be necessary for just the code I need.
Also attached image of the button you click - and the area that is red until checked off.

----------------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler

'Adjust Anodes sealing
If Cells(2, 55) = "Anode Sealing" Then
ActiveSheet.Shapes("Group 2073").Visible = True
ActiveSheet.Shapes("Rectangle 20").Visible = True
ActiveSheet.Shapes("ANODES1").Visible = True
ActiveSheet.Shapes("ANODES2").Visible = True
ActiveSheet.Shapes("ANODES3").Visible = True
ActiveSheet.Shapes("ANODES4").Visible = True
ActiveSheet.Shapes("ANODES5").Visible = True
ActiveSheet.Shapes("ANODES6").Visible = True
ActiveSheet.Shapes("ANODES7").Visible = True
ActiveSheet.Shapes("ANODES8").Visible = True
ActiveSheet.Shapes("ANODES9").Visible = True
ActiveSheet.Shapes("ANODES10").Visible = True
ActiveSheet.Shapes("ANODES11").Visible = True
ActiveSheet.Shapes("ANODES12").Visible = True
ActiveSheet.Shapes("ANODES13").Visible = True
ActiveSheet.Shapes("ANODES14").Visible = True
ActiveSheet.Shapes("ANODES15").Visible = True
ActiveSheet.Shapes("ANODES16").Visible = True
ActiveSheet.Shapes("ANODES17").Visible = True
ActiveSheet.Shapes("ANODES18").Visible = True
ActiveSheet.Shapes("ANODES19").Visible = True
ActiveSheet.Shapes("ANODES20").Visible = True
Else
ActiveSheet.Shapes("Group 2073").Visible = False
ActiveSheet.Shapes("Rectangle 20").Visible = False
ActiveSheet.Shapes("ANODES1").Visible = False
ActiveSheet.Shapes("ANODES2").Visible = False
ActiveSheet.Shapes("ANODES3").Visible = False
ActiveSheet.Shapes("ANODES4").Visible = False
ActiveSheet.Shapes("ANODES5").Visible = False
ActiveSheet.Shapes("ANODES6").Visible = False
ActiveSheet.Shapes("ANODES7").Visible = False
ActiveSheet.Shapes("ANODES8").Visible = False
ActiveSheet.Shapes("ANODES9").Visible = False
ActiveSheet.Shapes("ANODES10").Visible = False
ActiveSheet.Shapes("ANODES11").Visible = False
ActiveSheet.Shapes("ANODES12").Visible = False
ActiveSheet.Shapes("ANODES13").Visible = False
ActiveSheet.Shapes("ANODES14").Visible = False
ActiveSheet.Shapes("ANODES15").Visible = False
ActiveSheet.Shapes("ANODES16").Visible = False
ActiveSheet.Shapes("ANODES17").Visible = False
ActiveSheet.Shapes("ANODES18").Visible = False
ActiveSheet.Shapes("ANODES19").Visible = False
ActiveSheet.Shapes("ANODES20").Visible = False
End If

'Adjust Relining
If Cells(2, 55) = "Relining" Then
ActiveSheet.Shapes("Groupe 2").Visible = True
ActiveSheet.Shapes("Group 2060").Visible = True
Else
ActiveSheet.Shapes("Groupe 2").Visible = False
ActiveSheet.Shapes("Group 2060").Visible = False
End If

'Adjust SS Repair
If Cells(2, 55) = "Relining" Then
ActiveSheet.Shapes("Groupe 2").Visible = True
ActiveSheet.Shapes("Group 2060").Visible = True
Else
ActiveSheet.Shapes("Groupe 2").Visible = False
ActiveSheet.Shapes("Group 2060").Visible = False
End If

'Adjust Delining
If Cells(2, 55) = "Delining" Then
ActiveSheet.Shapes("Group 2059").Visible = True
ActiveSheet.Shapes("Group 2061").Visible = True
ActiveSheet.Shapes("Group 2068").Visible = True
Else
ActiveSheet.Shapes("Group 2059").Visible = False
ActiveSheet.Shapes("Group 2061").Visible = False
ActiveSheet.Shapes("Group 2068").Visible = False
End If

'Change la zone rouge en verte
If Feuil1.CheckBox1 = False Then Range("CI5:DC20").Interior.ColorIndex = 4 'CONTROL TEST


Dim KeyCells As Range
Set KeyCells = Range("AX17:AX46")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Dim Departement As String
Departement = Feuil1.Cells(2, 55)
Select Case Departement

Case "Anode Sealing"
Select Case Target.Value
Case "Workforce" 'R
Feuil3.Range("R4:R20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
Case "Operation" 'Q
Feuil3.Range("Q4:Q20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
Case "Equipement" 'P
Feuil3.Range("P4:P20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
End Select
Case "Relining"
Select Case Target.Value
Case "Workforce" 'AB
Feuil3.Range("AB4:AB20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
Case "Operation" 'AA
Feuil3.Range("AA4:AA20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
Case "Equipement" 'Z
Feuil3.Range("Z4:Z20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
Case "Rework" 'AC
Feuil3.Range("AC4:AC20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
End Select
Case "Delining"
Select Case Target.Value
Case "Workforce" 'U
Feuil3.Range("U4:U20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
Case "Operation" 'T
Feuil3.Range("T4:T20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
Case "Equipement" 'S
Feuil3.Range("S4:S20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
End Select
Case "Startup"
Select Case Target.Value
Case "Workforce" 'AF
Feuil3.Range("AF4:AF20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
Case "Operation" 'AE
Feuil3.Range("AE4:AE20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
Case "Equipement" 'AD
Feuil3.Range("AD4:AD20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
End Select
Case "Pot Replacement"
Select Case Target.Value
Case "Workforce" 'X
Feuil3.Range("X4:X20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
Case "Operation" 'W
Feuil3.Range("W4:W20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
Case "Equipement" 'V
Feuil3.Range("V4:V20").Copy Feuil3.Range(Cells(4, (Target.Row - 15) / 2).Address(RowAbsolute:=False, ColumnAbsolute:=False))
End Select
End Select

End If
ErrorHandler:
Resume Next
End Sub

--------

Hope this helps.. to help?

 

[Micron]

Well, that's not in code tags as I requested so not going to try and decipher that. Sorry.

 

[Inexperienced]

Not even sure what that means so thank you...