Excel 2007 : Highlight Text in one cell by clicking in another

[HangMan]

Is it not possible to highlight text in one cell using conditional formatting, by clicking in another cell.

Cell A1 has visible text in it, Cell B1 has white text in it so it appears hidden. When the user clicks in cell A1, I want to conditionally format the text in cell B1 to black, so the user sees it and then when they click any other cell, the text in cell B1 changes to white again, so it appears hidden.

I thought this would be easy using CF but I can't figiure a way to do this?

[HangMan]

If not, how would I do this using VBA?

[vandan_tanna]

VBA solution:

Please Login or Register  to view this content.

[HangMan]

Hi vandan_tanna,

Many thanks for your code, that works perfectly, however I've realised that I need to make a small addition.

There are two additional cells which the user needs to be able to also click without the text in cell B1 being hidden, so effectively, the text needs to show (as it currently does) when the user clicks in cell A1, but the user also needs to be able to click in cells E1 and F1 without the text in B1 being hidden, but any other cells clicked in, should hide the text again in B1.

How would I adapt he code to accommodate that? I tried adding those two cells to the target address, but this prevents the text from being shown when any of the three cells are clicked, what am I doing wrong?

Please Login or Register  to view this content.

[vandan_tanna]

Using address is easier for single cell but for multiple cells, I would do something like below:

Please Login or Register  to view this content.

[HangMan]

Vandan,

Many thanks, this works perfectly... greatly appreciated.

[HangMan]

Vandan,

Having just said that I realsied there is a further slight problem. The way it needs to work is that Cell A1 needs to be selected to show the text in B1, then if the user clicks in either cell E1 or F1, the text ramains shown, however, if the user clicks in cell say H1 and then clicks in either E1 or F1, the text in B1 shouldn't be shown. So in effect E1 and F1 are dependent on A1 having been clicked first. If the user clicks E1 or F1 the text in B1 shouldn't be shown, unless the user also clicks in cell A1.

Apologies, I think my original description was lacking, though I've only just realised this is a problem. I hope the above makes sense? Is this possible to do?

[vandan_tanna]

I think this is what you want...

Please Login or Register  to view this content.

alternatively, rather than changing the text color you can just put value in B1 with VBA like so...

Please Login or Register  to view this content.

[HangMan]

Hi Vandan,

I like your second approach, it is much neater and it seems to work perfectly...

Many thanks once again for your help...

[vandan_tanna]

You are welcome!