Conditional Formatting for Blank cells to display Custom text

[markinboone]

This seems like a bug to me (I have experience as a software tester, so I don't make this statement lightly). Or else there is some criterion that is grossly obfuscated. I searched a long time and could not find any documentation anywhere regarding the issue.

I have a range of cells with numeric values or blanks. I set up a Conditional Formatting rule with this range selected: New Rule > Format only cells that contain > Format only cells with: Blanks; then I set the formatting to a light green fill and on the Number tab > Category = Custom > Type: "blank"

The blank fields get the light green fill, but no text; that is, the value displayed is still blank.

I go back to edit the CF rule and change Blanks to No Blanks. The results are what I expect: the cells with numbers display the text blank and have a green fill; the blank cells have no fill (white).

This is my testing criteria. I eventually want the empty cells to display 0 (zero). I tried setting Custom > Type: 0 (the number placeholder zero) and Type: "0" (literally the number zero), and neither works.

I have tried this with Format only cells with Cell Value equal to [the address of a blank cell].

I tried to set the value as "" but kept getting =""""; and ="" became ="=""" ???

I have tried this with Use a Formula... using the len()=0, isblank(), and other approaches, all with the same results.

I also made sure the option [x] Show a zero in cells that have a zero value is checked.

With all approaches to identifying the blank cells, they ARE obviously being recognized as blank values: the fill color is being applied to the right cells; and when Not Blank is the criterion (or a negation of a formula), the blank ones are not formatted. But blank cells will not display the text as defined in the Custom Formatting.

And this isn't an issue of "If the cell displays 'blank' it's no longer blank, so the rule doesn't apply" - HERE'S WHY: I set up a second CF rule that sets the Font to red when the value is greater than zero and had this CF rule follow the CF rule for No Blanks (the test above that works). Cells with positive numbers displayed blank in red, cells with zeros or negative numbers displayed blank in black. This clarified that the actual value of the cell is being evaluated, not the displayed value after Custom formatting is applied.

Does anyone know how to accomplish this?

 

[jasonb75]

Welcome to the board!

If I'm following your point correctly, you are trying to change the content (value) of the cell with conditional formatting?

The purpose of CF, is to change the appearance of the cell and it's contents, not to actually change the contents.

A true blank cell has no content to change the appearance of, so number format manipulation will have no effect.

You could always populate the blank cells via vba, for example

Range("A1:Z99").SpecialCells(xlCellTypeBlanks).Value = 0

 

[markinboone]

Thanks for the reply. No, I don't need to change the stored value, just the format to display the text "blank" (or "0"). So that when a number value is cleared from the cell, it's displayed as "blank". But in preparing my clarification, I see how it will not work in Excel: A similar format would be a cell that stores a test grade and has the custom format: "Grade " #00. I set up a test with cells with grades and a blank cell. The cells with values display: Grade 98, Grade 100; the blank cell stays blank instead of displaying: Grade . So it's clear that the content cannot be formatted for a blank cell - which doesn't have any content. Logical, I suppose, but limiting. If a blank cell is evaluated as zero by formulas, I would think it could be formatted as if it were a zero value, displaying: Grade 0. I guess great minds don't all think alike. The VBA suggestion might work: I'm just not crazy about code in the sheet change event.