Make conditional format ignore blank cells.

I am trying to apply conditional formatting to a sheet, but the formatting is taking the inserted blank (but coloured) lines value as 0 and changing the colour of the particular cell on the inserted lines.

Does anyone know how to change a conditional format statement to ignore cells with no value in them?

Currently code :

Please Login or Register  to view this content.

How can I modify this to ignore the blank inserted lines/cells?

It appears your conditional formula is =V2<=Y2. Change it to (perhaps) =AND(V2<=Y2,?<>""), replacing the "?" with the blank cell reference (B2 perhaps).

Thanks for the quick reply.

Just tried it and getting 'Run time error '5''
Invalid procedure call or argument.

Is there a comma/bracket or speech mark missing somewhere? I have tried with the blank cell reference as B2 and A2, but both return the same error. It can test any cell in a row for a blank reference.

Originally Posted by jhren

Ahhh... scripting not my forte... IIRC, your have to concatenate the formula using ..."&CHAR(34)&CHAR(34)&"... inplace of the quotes in the formula. e.g.:
="=AND(V2<=Y2,B2<>"&CHAR(34)&CHAR(34)&")"

Yes, sorry, should have specified this was being carried out in a macro.

I tried your variation above and now VBA Editor is showing:

Compile Error
Sub or Function not defined

And it's highlighting the first instance of 'CHAR'.

I suspect we are very close.......

Perhaps it's CHR(34)?

Or try it like this...
="=AND(V2<=Y2,B2<>"""")"

If these don't work, I guess I am out of my elements.

Originally Posted by jhren

Perhaps it's CHR(34)?

That's the puppy! Thanks very much jhren. You have been my source of learning something new today .

I am at the moment learning MS Access too. One of my friends who is a VERY experienced DBA is teaching me, and he is quite enjoying it as a refresher to more beginner stuff he might have forgotten. Usually both sides benefit somewhere in the teaching! That's what I love about this forum.