Help with conditional formatting

In your conditional formatting criteria, use the following...

Format the cell as RED when

Formula is

and the formula will be

=(LEN(A2)>0)

i.e. if there are no text characters in cell A1, there will also be none in cell A2 (due to your formula) & hence the cell will not be red. If there is data in A1, A2 turns red...

Note that the cell will also be red if cell A1 contains a number is this what you need... :yes:

Hi Fluppe,

The Conditional Formatting for Cell A2 should be =ISTEXT($A1), with the formatting selected as red pattern. If cell A1 is zero or a number, cell A2 will remain white

Regards,

Bill

Hi Fluppe,

If cell A2 contains the formula =if(A1="","",A1), then the conditional formatting that I gave you will remain white if there is not text in cell A1. The conditional formatting does not recognise formulas in cells as text.

Regards,

Bill

Point of order here....Bill's right fluppe....

if his formula said =ISTEXT($A2) you would be correct in what you said here

Quote

I can't use the ISTEXT function because of the formula =if(A1="";"";A1) excel will format this cell as text when it is blank and will colour it red but the cell must be white when it is blank.

But Bill's formula refers to cell A1 which will not be TEXT if it's blank

Just so you understand that both solutions were valid... that's the great thing about Excel (&this forum) there's always more than one way to solve problems.... :tumble:

Edit: bah - too slow i am!! hey, and no offence meant fluppe :wink1:

heh heh :duck: you're welcome

Fluppe,
Conditional Formating (at least for EXCEL 2000 and earlier) does not allow references to other workbooks (or worksheets). Try referencing the "other wookbook" target cells by pulling them into a column in your active sheet (i.e., cell g1 in your active sheet = a1 in the target sheet). Then point your conditional formatting at the "copied" copied cell in your active sheet. The possible problem here is that unless you have both sheets active, your active sheet won't dynamically update. Otherwise I think this will work.