Do not apply formatting if cell is blank

[tmyers]

Conditional formatting is giving me a bit of a headache this morning.
I have conditions that if the cells are less than another cell, flag red. If they are greater than, green. The cells with the formatting have a IF SUM formula in them to set the cells to null if the formula returns a zero. So before anything is put in, the cells all turn green which I don't want. I tried adding ISBLANK to my conditions and made it the first rule, but it did not prevent the cells from turning green.

Anyone have any suggestions?

 

[Minty]

Blank cells in Excel are generally found using ""

Try setting the CF base on that first.

 

[tmyers]

Blank cells in Excel are generally found using ""

Try setting the CF base on that first.

I did try if cell is equal to "", but it still did not apply it.

 

[Isaac]

I did try if cell is equal to "", but it still did not apply it.

Upload a sample. too hard to tell what you're saying.

 

[Gasman]

Try an AND test ?

 

[tmyers]

Here you go. The column I am messing with is pretty obvious since it is all green. I have only been tinkering with the first row (row 2) cell in that column.

 

[Minty]

Add a rule for formatting only cells that contain...

and select "Blanks".

Make sure that you then also select stop if true.
Make it the first rule.

 

[tmyers]

Add a rule for formatting only cells that contain...

and select "Blanks".

Make sure that you then also select stop if true.
Make it the first rule.

The stop if true. That is what I kept overlooking. Thanks Minty!

 

[Isaac]

Here you go - I had to use len() instead of ="", as well as stop if true.

I also saved it as .xlsb....I recommend always using this file extension, it saves about 40% filesize over xlsx.

 

[Minty]

Here you go - I had to use len() instead of ="", as well as stop if true.

I also saved it as .xlsb....I recommend always using this file extension, it saves about 40% filesize over xlsx.

With being too lazy to google, what does .xlsb do or not do compared to a "normal" excel file?

 

[Isaac]

With being too lazy to google, what does .xlsb do or not do compared to a "normal" excel file?

It's perfection--it enables macros and all else is the same.

 

[Minty]

Isn't that the same as a .xlsm ?

(I'm hopefully fully demonstrating my lack of real Excel knowledge here )

 

[Isaac]

Isn't that the same as a .xlsm ?

(I'm hopefully fully demonstrating my lack of real Excel knowledge here )

Precisely. So you get the benefit of xlsm, except it doesn't have that 'scary look' of "oh this must have macros i wonder why", and you get a reduced filesize of around 40% most of the time (but not 100% of the time).

The point is the reduced filesize, which if you're passing things of size 1-5 MB around a network, is priceless. Esp. on VPN and remote..