Help with conditional formatting

  • Hi,
    A cell must change red when it contains text. The cell ie A2 must stay white when it is blank.
    The cell contains a link A2=A1
    The problem occurs when my cell contains the following formula ie =if(A1="";"";A1).
    When A1 is blank A2 is also blank but excel thinks its still text so it is coloured red.
    How can I solve this problem.
    Any idea?


    Thanx,
    Fluppe

  • 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 WillR,
    This works and I can use it because the cell can't contain a number.
    Thanx


    Hi Ranger,
    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.
    Thanx

  • 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.... :P :tumble:



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

  • Hi again,
    I think I found another problem.
    It doesn't seem to work when the formula links to a cell in another workbook =if('[WorkbookA1.xls]sheet1'!A1="","",'[WorkbookA1.xls]sheet1'!A1) what can I do about this?

  • 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.