Conditional Formatting - "Blank Cell" doesn't work 2

[MattGreer]

So I've got a spreadsheet that needs certain fields filled out. If they're not, I want the background of the cell to be filled/shaded. I've done this before on another spreadsheet, so I copied the formatting and pasted it into this worksheet and it didn't work. So I cleared all formatting from the cell, and then applied the conditional formatting manually. If the cell is blank, shade it [selected color]. Doesn't work. But if I set the conditional formatting to be: if the cell equals zero, shade it [selected color]. In this case, when the cell is blank it gets shaded. And when the cell has the number zero in it, it gets shaded.

The rule reads "Cell contains a blank val..". What is a blank value? lol Maybe that's the problem, but the conditional formatting is applied in the same way the other spreadsheet that functions correctly is applied.

I'm using 64 bit excel. The spreadsheet was created in an older version of excel, as an xls file, and then I just converted it to the latest and greatest format. I figure that's probably what's causing the issue but I don't see how. The conditional formatting didn't work in the old format, nor the new.

When I copy and paste the contents of the sheet into a new workbook, the conditional formatting works. What is in this spreadsheet that is preventing the conditional formatting from activating, do you think?

I'm most definitely confused.

Thanks!!


Matt

 

[Deniall]

I don't have any trouble conditionally colour-filling a blank cell using the =ISBLANK(cell-address) formula in the conditional format. I am using Excel-2010 on a 64-bit Windows-10 laptop.

 

[combo]

Excel 2016, 32 bit: blank in CF condition means no visible character, i.e. the cell has no formula or the result of formula returns no visible character (as filled with a series of spaces or formula [tt][highlight #D3D7CF]=" "[/highlight][/tt]), both will be formatted as blank.
Go to CF rules management dialog, select worksheet rules scope and analyse formatting for cells that you expect that should be displayed differently.

combo

 

[Gavona]

Try testing for len(cell-address)=0

It can be easier to test your cf formula in a normal worksheet cell. So put that formula in a worksheet cell. If you can't see what the content of these not-blank cells is you can also put in a cell =char(my-cell).
Then decide whether you want to clean up these cells before you do the formatting or simply cope with them within you conditional formatting.

Gavin

 

[MattGreer]

Thanks folks, I'll definitely test that out.

Thanks!!


Matt