Conditional formatting formula for empty cells

[kukerjoe]

Hi,
i have a range OF CELLS that MY USERS need to fill with data.
THE RANGE IS from A1:A1000.
sometimes they skip rows for example they fill the cell A1 and then they skip CELL A2 AND LEAVE IT BLANK and THEN THEY fill A3.
I need a CONDITIONAL FORMATTING formula to fill in yellow all those empty CELLS ,cell A2 IN THE EXAMPLE BUT NOT THE CELLS BELOW A3.
IF THEY CONTINUE TO FILL CELLS AND SKIP A4 AND FILL A5 AND I NEED THAT A4 WILL BE FILLED IN YELLOW TOO.
THE CONDIONAL FORMATTING WILL BE APPLIED TO RANGE A1:A1000.

 

[Rick Rothstein]

Select range A1:A1000 with cell A1 being the active cell, then use this for the Conditional Formatting formula...

=(ROW(A1)< SUMPRODUCT(MAX(ROW(A$1:A$1000)*(A$1:A$1000<>""))))*(A1="")

 

[wideboydixon]

I think something got lost in that formula but what I did was:

1. Highlight A1:A1000
2. Create a new conditional format based on the following formula:

=AND(A1="",COUNTA(A1:$A100)>0)

WBD

 

[Rick Rothstein]

I think something got lost in that formula but what I did was:

1. Highlight A1:A1000
2. Create a new conditional format based on the following formula:

=AND(A1="",COUNTA(A1:$A100)>0)

WBD

A much better formula than mine; however, you left out a 0 in the range and put the $ sign in the wrong place (you typed $A100 when I think it should be A$1000). I believe your formula should be this...

=AND(A1="",COUNTA(A1:A$1000)>0)

 

[wideboydixon]

A much better formula than mine; however, you left out a 0 in the range and put the $ sign in the wrong place (you typed $A100 when I think it should be A$1000). I believe your formula should be this...

=AND(A1="",COUNTA(A1:A$1000)>0)

Yep. Thanks. Not quite woken up yet it would seem

WBD