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

Thank you,

Brian

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

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

JW, hows the weather up there in your chair.

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

Now what exactly is that supposed to mean? I wasn't trying to step on
any toes or ruffle any feathers, but I apparently I did a poor job at
that. We are all on these news groups to learn and to share. Time to
grow up.

Regards,
-Jeff-

Texas Aggie wrote:
JW, hows the weather up there in your chair.

Maybe you should take some of your own advice.

Now that's funny. I have been critiqued several times on this
newsgroup since joining a while back. Never took any of it to heart.
I took it as people trying to help me grow and improve my coding. If
you will go back and read my original response, there is nothing
malicious or harmful in anything that I said.

All that being said, I'm done. To the OP, I hope that you got your
question answered.

Texas Aggie wrote:
Maybe you should take some of your own advice.