Data Validation to circle blanks cells between filled cells

[jamiem4]

Hey,

Im looking for a Data Validation formula that will allow be to circle any instances in a column of data of variable length where there is a blank cell within the column (ie doesn't circle all of the blank cells after the data has ended).

Any help would be greatly appreciated.

Thanks,
Jamie

[Olly]

Using Cell A1 as an example:

Please Login or Register  to view this content.

Format to suit, apply to relevant range.

[Olly]

File attached, as a working example. The same conditional formatting formula applies to A1:D14 in this example.

[jamiem4]

Thanks for your help, that seems to work like I need it to.

I was wondering if you'd be able to help me integrate it into the following Data Validation formula that I have:

=AND(SUMPRODUCT(0+(ISNUMBER(0+MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1))))=LEN(A1)-1,ABS(77.5-CODE(RIGHT(A1)))<13)

This formula checks that text is in the format (for example): 1234A. I also need the formula to check if the cell is blank, but only if it's between or next to a non-blank cell.

I hope this makes sense!

Thanks,
Jamie