How to set up checkboxes on 250 rows that toggles the entire row color when clicked?

I'm not that knowledgeable with Excel, but I have to have this spreadsheet set up as a template to use for many projects.

It needs to have a check box on the beginning of each of 250 or so rows, and when the checkbox is checked it turns the entire row green and when unchecked turns the entire row back to white.

Is there any easy way to set this up?

Thanks a TON in advance... I've been Googling this for hours...

So how do i get it to run on column 1 and rows 20-270? etc? which parameters do i change?

yeah... it worked! thanks! now I just gotta figure out how to easily place it...

Well, the code you gave me works.. the file i downloaded of yours doesnt, it's a button that doesnt work...

So if I modify the code to this:

Sub AddCheckboxes()
    Dim c As Range
    Dim CkBx As CheckBox
    Dim Rws As Long, Rng As Range

    Rws = Cells(Rows.Count, "A").End(xlUp).Row

    Set Rng = Range(Cells(1, 20), Cells(Rws, 1))
    For Each c In Rng.Cells
        c.Select
        Set CkBx = ActiveSheet.CheckBoxes.Add(c.Center, c.Middle, c.Width, c.Height)

        With CkBx
            .Caption = ""
            .OnAction = "SetToGreen"
        End With

    Next c

End Sub

Sub SetToGreen()

    Dim CkBx As CheckBox
    Dim CkRw As Integer
    Dim CkRng As String

    CkName = Application.Caller
    Set CkBx = ActiveSheet.CheckBoxes(CkName)

    CkRw = CkBx.TopLeftCell.Row
    CkRng = "A" & CStr(CkRw)

    If CkBx.Value > 0 Then
        Range(CkRng).EntireRow.Interior.Color = vbGreen

    Else
        Range(CkRng).EntireRow.Interior.ColorIndex = xlNone


    End If

End Sub

Would that create the checkbox on A20? and place it Centered in Middle of cell?

How would I get it to do like A20 through A270? and Centered in Middle of cell?

K.. that code i did doesnt work

I'm confused on this code. What controls where it starts? It seems you can only control how many across or how many down.. but not what cell to start in...

Am I missing something?

Also, changing the "c. Left, c.Top " throws an error... so what controls the alignment in the cell?

Originally Posted by Domeno

So if I modify the code to this:

Sub AddCheckboxes()
    Dim c As Range
    Dim CkBx As CheckBox
    Dim Rws As Long, Rng As Range

    Rws = Cells(Rows.Count, "A").End(xlUp).Row

    Set Rng = Range(Cells(1, 20), Cells(Rws, 1))
    For Each c In Rng.Cells
        c.Select
        Set CkBx = ActiveSheet.CheckBoxes.Add(c.Center, c.Middle, c.Width, c.Height)

        With CkBx
            .Caption = ""
            .OnAction = "SetToGreen"
        End With

    Next c

End Sub

Sub SetToGreen()

    Dim CkBx As CheckBox
    Dim CkRw As Integer
    Dim CkRng As String

    CkName = Application.Caller
    Set CkBx = ActiveSheet.CheckBoxes(CkName)

    CkRw = CkBx.TopLeftCell.Row
    CkRng = "A" & CStr(CkRw)

    If CkBx.Value > 0 Then
        Range(CkRng).EntireRow.Interior.Color = vbGreen

    Else
        Range(CkRng).EntireRow.Interior.ColorIndex = xlNone


    End If

End Sub

Would that create the checkbox on A20? and place it Centered in Middle of cell?

How would I get it to do like A20 through A270? and Centered in Middle of cell?

I finally got it working, BUT, when you save the file, close it, then reopen it... it fails to turn columns green when checked... how do you get it to save that functionality?

k.. i figured it out! Apparently, the code was tied to the test sheet that was open. So when I closed them all, the code was not on the saved "real" sheet... I had to redo it all, but it worked fine and worked fine after saving...

Thanks a TON for all the help davesexcel!!!

I have another question... how can i run a script to delete all the hundreds of boxes? I'm running into a problem where if I copy and paste the sheet into new tabs, the checkboxes can be messed up easily.... so it seems that it would be easier to delete them all then recreate them?