Conditional Formatting Not Blank Cells

[Nate]

Hello,

I'm Using XL 2003. I would like to apply a conditional format to the range
of cells A2:F171. Basically, I would like to highlight all of the rows that
have a value in column F, and the rows that have no value or are blank in
column F should remain unchanged. I tried using the formula
=NOT(ISBLANK($F2)). But it highlights all cells in the array. Any
suggestions would be greatly appreciated. Thanks,

Nate

 

[PCLIVE]

If you have formulas in those other cells, then they are not blank. If that
is the case, then use this formula instead.
=NOT($F2="")

HTH,
Paul

 

[Pete_UK]

Or even:

=$F2<>""

Pete

If you have formulas in those other cells, then they are not blank.  If that
is the case, then use this formula instead.
=NOT($F2="")

HTH,
Paul

--








- Show quoted text -

 

[Nate]

Sorry - I don't think I explained clearly. There are no formulas in column
F. Some cells have dates in them and some are blank. I was highlighting the
whole range and then doing Format > Conditional Formatting > Formula is >
=NOT(ISBLANK($F2)). I guess I'm confused as to why I would need a formula in
the cell itself to do a conditional formatting? Thanks-

 

[PCLIVE]

I wasn't saying that need a formula in the cell. I was actually referring
to your Conditional formatting (Formula is). My point was that if there
were formulas in those cells in which the formula returns a blank
=IF(A1="","",A1) then the cell would appear blank.
Is it possible that the cell is not actually blank...maybe there's a space
in the cell. Test a cell that appears to be empty but still gets
highlighted with your conditional formatting. In another cell enter this
formula: =LEN(YourCellReference)
If that returns anything but zero, then the cell is not blank.

HTH,
Paul

--

 

[Nate]

I tested using the formula you provided and all cells that appear blank
actually are blank (or at least they are returning 0). When I highlight the
whole range A2:F171 and then enter formula is =NOT(ISBLANK($F2)) - it
highlights ever cell in my range. Column F is the only column with any blank
cells. I'm just not sure if my formula is wrong or if I'm doing something
else wrong. Thanks for all of your input.

 

[Gord Dibben]

Works for me "if" the look-like-blanks in Column F are truly blank.

Have looked to see if perhaps Excel changed the $F2 to F2 or $F$2 in your
formula is: =NOT(ISBLANK($F2))


Gord Dibben MS Excel MVP

 

[Nate]

I went through all the blank cells and used the delete function in all of
them. The formula works now, which is weird since I checked multiple times
to make sure that there were no formulas or blank spaces in the empty cells.
Thanks for all your help-