A simpler way:
Sub CheckBox1_Click()
Dim colorRange As Range, chkBoxName As String
chkBoxName = "Check Box 1"
Set colorRange = ActiveSheet.Range("A2:A5")
For Each CheckBox In ActiveSheet.CheckBoxes
If CheckBox.Name = chkBoxName Then
If CheckBox.Value = 1 Then
colorRange.Interior.ColorIndex = 36
Else
colorRange.Interior.ColorIndex = xlNone
End If
Exit For
End If
Next CheckBox
Set colorRange = Nothing
End Sub

JW wrote:
Texas Aggie, selections such as that are considered bad coding
practice unless absolutely necessary. Also, that code will always do
the same thing, regardless of if the checkbox is checked or not.
Should use something like this (assuming the checkbox was added from
the Forms toolbar).
Sub CheckBox1_Click()
Dim colorRange As Range, chkBox As Shape
Set colorRange = ActiveSheet.Range("A2:A5")
For Each chkBox In ActiveSheet.Shapes
If chkBox.Name = "Check Box 1" Then
If ActiveSheet.CheckBoxes(chkBox.Name) _
.Value = 1 Then
colorRange.Interior.ColorIndex = 36
Else
colorRange.Interior.ColorIndex = xlNone
End If
Exit For
End If
Next
Set colorRange = Nothing
End Sub

Texas Aggie wrote:
attach the check box to this macro

Sub Changebg()
Range("A1:A10").Select
With Selection.Interior
.ColorIndex = 6 'yellow
.Pattern = xlSolid
End With
Sheets("Sheet1").Select
End Sub


--
If this reply was helpful, please indicate that your question has been
answered to help others find anwsers to similar questions.

www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009


"Brian Smith" wrote:

How do I set a check box to change the background color of a range of cells?

Thank you,

Brian