CODEWITHSUNDEEP
excelvba
Iancovici
Iancovici

Reputation: 5731

How to change row color based on a checkbox status change?

I've been trying several codes when editing macro of a checkbox but none seem to have an effect, and I always get error 424 something about missing object enter image description here

so say it's Checkbox44_OnClick() I can't use Checbox44.value or at all

Any ideas how I can change row color based on a checkbox status?

Sub CheckBox44_Click()
    If Checkbox44.Value = True Then
        Worksheets("Sheet1").Range("8:8").Interior.ColorIndex = 36
    End If
End Sub

Upvotes: 0

Views: 3223

Answers (2)

Sambit
Sambit

Reputation: 1

IF the sheet is password protected then this error appears.

Try the following:

Sub CheckBox44_Click()
Sheet1.Unprotect ("Your Password")
    If Checkbox44.Value = True Then
        Worksheets("Sheet1").Range("8:8").Interior.ColorIndex = 36
    End If
Sheet1.Protect ("Your Password")
End Sub

Upvotes: 0

Mitja Bezenšek
Mitja Bezenšek

Reputation: 2533

There are a few different types of check boxes available in Excel (Form controls, ActiveX controls). It seems that you are using the forms controls check box. Try this code:

Sub CheckBox44_Click()
With ActiveSheet.CheckBoxes("CheckBox44")
  If .Value = xlOn Then
     Worksheets("Sheet1").Range("8:8").Interior.ColorIndex = 36
  End If
End With
End Sub

Make sure that the name of your checkbox is correctly set (check the Name box when the check box is selected):

enter image description here

Upvotes: 2

Related Questions