×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

Conditional Formatting - "Blank Cell" doesn't work

Conditional Formatting - "Blank Cell" doesn't work

(OP)
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

RE: Conditional Formatting - "Blank Cell" doesn't work

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.

RE: Conditional Formatting - "Blank Cell" doesn't work

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 =" "), 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

RE: Conditional Formatting - "Blank Cell" doesn't work

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

RE: Conditional Formatting - "Blank Cell" doesn't work

(OP)
Thanks folks, I'll definitely test that out.

Thanks!!


Matt

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close