Trying to change the color of a row based on a check box

Question

Hi, I am new to excel and I'm trying to figure out how to get a row to change color based on if a check box is clicked or not. I've gotten the check box to the correct cell, and now I'm just trying to link it to the row. I want it to be red if the check box is unclicked and green if it is clicked. The document I'm working on will be an expanding list/table that will be added to overtime for the project. So far I'm working in cells C5 down to L23. I've looked online to try and figure out a solution to this and so far no luck. Any help would be greatly appreciated thank you.

Answer 1

Is your workbook named Book1.xlsm?

If so, where did you create the macro CheckBox3_Click?

Answer 2

Make sure that the top left corner of each check box is in the row you want to color.

Create the following macro in a module in the Visual Basic Editor:

Sub CheckBoxClick()
    Dim nme As String
    Dim shp As Shape
    Dim rng As Range
    Dim clr As Long
    nme = Application.Caller
    Set shp = ActiveSheet.Shapes(nme)
    Set rng = shp.TopLeftCell
    If shp.ControlFormat.Value = 1 Then
        clr = vbGreen
    Else
        clr = vbRed
    End If
    rng.EntireRow.Interior.Color = clr
End Sub

Assign this macro to all of the check boxes.

I tried your code, and now I'm getting an error response saying "Cannot run the macro "Book1.xlsm!Checkbox3_Click. The macro may not be available in this workbook or all macros may be disabled.

I've gone through the trust settings and enabled macros, so I'm not sure what to do from here.

Thank you for your help so far, it's greatly appreciated.

Answer 3

Make sure that the top left corner of each check box is in the row you want to color.

Create the following macro in a module in the Visual Basic Editor:

Sub CheckBoxClick()
    Dim nme As String
    Dim shp As Shape
    Dim rng As Range
    Dim clr As Long
    nme = Application.Caller
    Set shp = ActiveSheet.Shapes(nme)
    Set rng = shp.TopLeftCell
    If shp.ControlFormat.Value = 1 Then
        clr = vbGreen
    Else
        clr = vbRed
    End If
    rng.EntireRow.Interior.Color = clr
End Sub

Assign this macro to all of the check boxes.

Answer 4

Everything I've read has shown to use Form Control check boxes, so that's the only ones I've tried so far.

Answer 5

If you enter a symbol, for example an "x" in a cell instead of ticking a check box, you could use conditional formatting.

If you prefer using check boxes, you'll have to rely on VBA code to color the rows.

Which type of check boxes are you using? Form Control check boxes or ActiveX check boxes?