I need to highlight (color-fill) a cell when a checkbox is clicked an
then be able to unclick and have the cell have no-fill.

anyone know how to do this?

Cadbur

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Where is your checkbox? On UserForm or Worksheet?

Anyway, to color a cell, for example, yellow:

range("A1").Interior.ColorIndex = 6

To remove the color:
range("A1").Interior.ColorIndex = xlNone

To discover more "colorindex", you may try using the "Record Macro" function of Excel.

In fact, the Record Macro function is a very very good way to learn VBA.

Regards,
Edwin Tam

The Checkbox is in a worksheet

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

So, create the following macro in a module, then assign it to the checkbox on your worksheet.

Sub CheckBox1_Click()
With ActiveSheet.CheckBoxes("Check Box 1")
If .Value = xlOn Then
Range("A1").Interior.ColorIndex = 6
Else
Range("A1").Interior.ColorIndex = xlNone
End If
End With
End Sub

----- cadbury wrote: -----


The Checkbox is in a worksheet.

Cadbury,

Here is an approach where you don't need VBA for this.
You can use a linked cell in combination with conditional
formatting.

1. Create the checkbox

2. If the checkbox is from the "Forms" toolbar:
- Right-Click the checkbox and select "Format Control"
- Select the Control tab
- Set cell link to A1
- Click OK

If the checkbox is fron the "Control Toolbox" toolbar:
- Right-Click the checkbox and select "Properties"
- Set the cell link property to A1
- Close the properties window
- Click the "Exit Design Mode" toolbar button

3. Select the cell you want to color

5. Format - Conditional Formatting
Formula is... =A1=TRUE
Click Format, set Red color on Patterns tab

6. Click OK, twice

--
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden

"cadbury" wrote in message
...

I need to highlight (color-fill) a cell when a checkbox is clicked and
then be able to unclick and have the cell have no-fill.

anyone know how to do this?

Cadbury


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

The non-VBA is the better way for me to go, but the cell that highlights
also says "true" or "false", depending on the checkbox. How can I make
it so true or false doesn't replace the text already in the cell.

Thanks for your help

Cadbury


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

I figured out a solution. I made the text color part of the conditional
formatting, the true and false are still there, they just blend in with
the cell color. If there is a better way, please let me know.

Cadbury


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Cadbury,

I often set the checkbox's cell link to the cell behind the
checkbox and set the number format of that cell to the
custom format ;;;

--
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden

"cadbury" wrote in message
...

I figured out a solution. I made the text color part of the conditional
formatting, the true and false are still there, they just blend in with
the cell color. If there is a better way, please let me know.

Cadbury


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/