Conditional Formatting With Formulas

Try...
=ISTEXT(A1)

And please a) upload to the forum and not to Dropbox (not everyone will download from other sites/services) and b) please stop uploading pictures of spreadsheets and upload sample workbooks instead. It is much easier to investigate and diagnose problems and propose solutions with a workbook.

Regards, TMS

Sorry, I'm new in the forum and thank you very much for your help.

Here is the file. The example is on the sheet named NonNumeric.

As has been suggested:

Formula:

Please Login or Register  to view this content.

or, to copy with spaces (as opposed to blank/null)

Formula:

Please Login or Register  to view this content.

Some good examples by the way


Regards, TMS

A CF formula always refers to the top left cell of the range.

Am I right in thinking you're coming back some 10 months after we last answered your question? And now you're saying it "won't work"

Whatever. Look at the formula. It does not refer to cell A1; you have

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

Regards, TMS

If the range may have empty or null cells that you don't want formatted until filled, select the whole range (I'm assuming a start in A1 and using the range A1:A200). The range does not show in the formula but only in the Applies To after the rule is created.
For the numbers:

Formula:

Please Login or Register  to view this content.

For the text:

Formula:

Please Login or Register  to view this content.

Is the test for <>"" necessary?

@Ron: the problem is that the formula used does NOT refer to cell A1 ... although that was what was advised originally.

@Tony: probably not, but I don't think it hurts.

I set up error situations in an extended range where the result was "" and they became formatted as text. So, I included the <>"".

I exactly did what I say. If you follow what I do from scratch you get the exact situation.

However If you set up a rule once and then try to edit and refer "A1" it works strangely.

How to do it in a one step?

CF works on an array basis. You tell it where to start and what the range it - then tell it what the rule/s is/are...it takes care of all the rest.

If your range is A1:J10, and your rule is =A1=10, if you click on J10 and look to see what the rule there is, it will still say =A1=10 but that has been appllied to the entire range

Select cell A1.
Hold the Shift Key and press End and Down Arrow
(this will select the cells in column A, from cell A1 down)
(Note that the Active Cell is cell A1)
Now select Conditional Formatting
Select Use Formula
Type in one of the formulae that have been suggested.
OK
Apply
Done

Originally Posted by zanshin777

I exactly did what I say. If you follow what I do from scratch you get the exact situation.

However If you set up a rule once and then try to edit and refer "A1" it works strangely.

How to do it in a one step?

It looks like when you put A1 into the Format values where this formula is true: formula box you

edited $A$1 by deleting / backspacing the $ out. Format manager then changes that to A1048576.

It appears to be an idiosyncrasy of Format manager. Instead of editing the $A$1 press the F4 function

key repeatedly until you get the relative / absolute reference you want. It holds the address then.

Thanks for the rep.