Question re. conditional formatting cells with formula

**kborgers** · 06-22-2012, 03:45 AM

Hi All,
A simple question; I presume there is a rule that states that if you format a cell dependent upon whether it contains text (=ISTEXT(cell_ref)) and that cell has formula, Excel assumes is has text because of the formula and therefore formats it anyway? I attached an example file.

**Spencer101** · 06-22-2012, 04:05 AM

Hi,

On Sheet2 of the attached I've put in a very simple vlookup formula that returns one text value and one numeric value. Then applied your conditional formatting using ISTEXT. Seems to work fine and the formatting is applied to the text value and not to the numeric one.

Did you get a different result when you tried it?

**kborgers** · 06-22-2012, 04:30 AM

Hi Spencer, in col A, I have as you've seen, an array formula that is copied down to row 29. I also have CF in A4 set as ISTEXT(A4)´, then draw a border. If I format paint that down to row 29, every cell shows a border. In Col C, I have a few rows of text. I format painted from A4 into C4 and down to C29 and only the cells with text have a border. Does this make sense?

**Fotis1991** · 06-22-2012, 04:05 AM

Hi

My suggestion is to use, in CF rules something like this.

=$A4<>""

**kborgers** · 06-22-2012, 04:32 AM

Hi Fotis, you have every right to be proud of Greece - beautiful country and people! Also, the land of Excel CF solutions! Your suggestion works perfectly - thanks!! I still cannot figure out why ISTEXT() does not though!

**Fotis1991** · 06-22-2012, 04:52 AM

You are really welcome!

Thank you for you feed back, your good words and also for the reb*

To be honest, watching the previous answer(post#4) and believing that you ignore my reply, I did not expect to have such a good image for my country and its people.

Thank you.

For your question for ISTEXT,honestly i don't know why does not work. It's real strange for me too.