I throw down the gauntlet to the excel guru's. I’m designing a worksheet with multiple pages and on the one page there are a few columns with tick boxes (yes, no and n/a) now here’s the fun part. What I want to happen is if someone checks the box “no” I want a comment box to turn red but for the life of me can not think of the equation to use. I have tried using the conditional formatting and format control on the tick box and the best I have managed to do is turn the tick box red and the comment box to say true if the box is checked and false if the box is unchecked. Someone has told me I need to use macros as i have never used them I don't want to play with them yet….

can you clarify what you mean by "comment box".

do you mean a comment attached to a cell, the format of the tickbox caption or a linkedcell to the tick box?

As you can see from the screen grab there is a comment box, thats is going to be one of two comment boxes. what the power to be wants is if check the No box for the Billet bank the comment box turns red telling me i have to put a reason in the box. It's not me that wants it this way it's the nugget called a manager in the next office.

mmmm maybe not the best screen grab try

http://i194.photobucket.com/albums/z199/Redlen17/kpi.jpg

OK, that's easy then.

assuming you want any No flag to turn the commenst box red, then what you need to do is:

for each "No" tickbox control, set its LinkedCell field to the cell that's behind it. This cell entry now will alternate between TRUE and FALSE depending whether the box is ticked or not. Set the font colour to your background colour if you want to hide it.

Now, in your comments box under conditional formatting use something like:
=COUNTIF((A1:A3), FALSE)>0 in the Formula Is box. Where A1:A3 is the array that reprsents all your linked cells you want to test for.

oops, better if I read properly,

you only then need to link the Billet Bank No control to the cell behind it and then simply make the conditional format look at that cell:

=C57=TRUE

use conditional formating

Stoner your a star. Easy when you know how. Thank you very much……

Im glad Ive managed to do one thing useful today.

Might go for a poo on company time just to be really productive.