[scouse13]

can anyone help please,

i have a few checkboxes on a worksheet and what i would like to do, is when a checkbox is checked, is for a range of cells to change colour, i.e b6:b9, also once i have finished with the worksheet and press a reset button for the cells to revert back to their origional colour.

[bomb #21]

Linking the check box to a cell (e.g. B2) will give you TRUE when checked, FALSE when unchecked.

Then use a conditional formatting formula for B6:B9 such as:

=$B$2=TRUE

HTH

(red or blue scouse? )

[scouse13]

thankx bomb21
i dont want to use a button to make the changes i want it to happen automattically when the check box ix checked, whats the code and where do i put it
thanks
also stupid as i am, where do u check for the code for colours

[Rollin_Again]

The checkbox needs to be added from the Control Toolbox and NOT the Forms Toolbox. Once added you can right click it and choose VIEW CODE. Then just add your code to the change event. Just replace the name of the textbox in line 1 with the actual name of your own checkbox.

Private Sub CheckBox1_Change()

Range("A1:C1").Interior.ColorIndex = 6

End Sub

Regards,
Rollin

[bomb #21]

Originally Posted by scouse13

thankx bomb21
i dont want to use a button to make the changes i want it to happen automattically when the check box ix checked, whats the code and where do i put it
thanks
also stupid as i am, where do u check for the code for colours

I don't understand your post.

1. I didn't mention using a button at all.

2. If you use a Control Toolbox checkbox, you don't need any (VBA) code specifically, you just link it to the cells (to colour) using the LinkedCell property. If you use a Forms toolbar checkbox, you don't need any code at all.

3. I guessed that you already have a checkbox on your sheet. You didn't say which type you'd used, so I made the "instructions" basic enough that they'd work for either type (I did test this)

4. Rollin's code (assuming you know how to exit Design Mode) will format A1:C1 with a yellow background when you check the box. But it won't "unformat" when you uncheck the box -- you'd need additional code for that.

HTH

[Rollin_Again]

Per Bomb's comment above, here is the modified code that includes logic to uncolor the cells when the checkbox is unchecked.

Private Sub CheckBox1_Change()

Select Case CheckBox1.Value

Case True

Range("A1:C1").Interior.ColorIndex = 6

Case False

Range("A1:C1").Interior.ColorIndex = xlNone

End Select

End Sub

Regards,
Rollin

[scouse13]

thanks rollin
works a treat, just 2 things, firstly i want to be able use a control button to automatically uncheck all the checkboxes rather than to click each one individually, what would the code be for this to happen? also were can i check what numbers refer to the colours, i have the cells now that are coloured using the drop down colour box, they also have a fill effect, when the checkbox is unchecked i would like the cells to refer back to these settings,
hope u understand my rambling message
thanks scouse

[bomb #21]

To automatically uncheck all the checkboxes:

Me.CheckBox1 = False
Me.CheckBox2 = False

and so on. ( http://www.ozgrid.com/forum/showthread.php?t=48933 )

"were can i check what numbers refer to the colours"

Here's one way to create an index:

Sub test()
For Each Cell In Range("A1:A56")
Cell.Interior.ColorIndex = Cell.Row
Cell.Offset(, 1) = Cell.Row
Next Cell
End Sub

HTH

[Rollin_Again]

Or if you prefer to use a loop you can use the code below.

Private Sub CommandButton1_Click()

Dim vCheck As Object

For Each vCheck In ActiveSheet.OLEObjects
If TypeName(vCheck.Object) = "CheckBox" Then
vCheck.Object.Value = True
End If
Next vCheck

End Sub

Regards,
Rollin