Conditional Formatting using Checkboxes

[dagindi]

I am using the following code to insert multiple check boxes at once.

Please Login or Register  to view this content.

If you apply this code to B1:B5 and then tick off B3, B3 will turn yellow.

How do I change this code so that when I tick off B3, A3 (or whatever cell I want) will turn yellow or whatever color I want it?

[Kafrin]

The change you need is to this line:

Please Login or Register  to view this content.

Basically you put the condition on the cell you want to change colour. From your example, you could try something like:

Please Login or Register  to view this content.

You selection must include the cell you want to change colour. This example will make the first column (A) change colour if the second column (B, offset one column from A) is ticked.

Don't forget you can add more than one condition to a cell as well.

[dagindi]

Kafrin,

Thanks for your help.

I should have mentioned that I am a bit of an excel noob and that I am using Excel 2007.

I altered the code per your post as follows:

Please Login or Register  to view this content.

I ran it once in coloum A and once in coloum B.

See attached for the results.

What part of the code directs the checkbox to highlight the target cell?

Also where can I find a list of format conditions and the coding. For example ".FormatConditions(1).Interior.ColorIndex = 6" means the fill will be yellow. Where can I find a list of colors and their cooresponding numbers?

[Kafrin]

What part of the code directs the checkbox to highlight the target cell?

It's not intuitive, but you need to think the other way around - the question should be "What part of the code directs the target cell to look at the state of the check box?"

The answer is, the "c.Offset(0,1)" part. This means from the target cell, look at the cell one column to the right. You can change the offset numbers as needed.

I'm not sure if Excel includes a colour index list - if you customise any of the colours they'd change anyway. I usually record a macro in which I type the name of a colour into a cell then change teh cell to that colour, and repeat for as many colours as I need the numbers for. Then stop the macro and look at the code to get the numbers.

Alternatively, you can use Color instead of ColorIndex and there are some built-in options for this (e.g. XLRed I think). Or you can use Color and the RGB function to specify any colour you like, e.g. Interior.Color = RGB(0,0,0) would turn a cell black.