conditional formatting for rows with blank cells

[mbnott]

My spreadsheet has data in columns A:AD. I already have a CF rule to highlight blank cells, but due to the size of the sheet, it can be hard to visually catch all of them. So, I want a CF rule to highlight the cell in column A if that cell contains data and any of the cells in C:AD have any left blank.

 

[Joe4]

Your Conditional Formatting formula would look something like this for cell A1:

=AND($A1<>"",COUNTBLANK($C1:$AD1)>0)

You would just select column A and apply this CF formula, and choose your desired formatting.

 

[mbnott]

Your Conditional Formatting formula would look something like this for cell A1:

=AND($A1<>"",COUNTBLANK($C1:$AD1)>0)

You would just select column A and apply this CF formula, and choose your desired formatting.

That worked perfectly, thank you! Secondary question...I had another CF rule to just highlight all blank cells. Naturally, this highlights every row even below my data. How can I highlight blank cells only if data is entered into column A? Basically, if I entered a date into column A like I'm starting an entry, but then any cell from B:AD gets left blank then those blank cells are highlighted to show me that something is missing.

 

[Joe4]

So, you could select columns B:AD, and enter this Conditional Formatting formula:

=AND($A1<>"",B1="")

Note how the column A reference is locked down to column A, so every cell looks at column A to see if there is a value.
But the column B reference "floats", so column B looks at the entry in column B, column C looks at the entry in column C, etc.

 

[mbnott]

So, you could select columns B:AD, and enter this Conditional Formatting formula:

=AND($A1<>"",B1="")

Note how the column A reference is locked down to column A, so every cell looks at column A to see if there is a value.
But the column B reference "floats", so column B looks at the entry in column B, column C looks at the entry in column C, etc.

That didn't quite work right. I don't think I explained it quite right. The only dependency should be on whether data is in A. Then look at each adjacent cell independently to highlight if it is left blank. No different than the 'highlight blank cells' basic rule, but do that only if something is in A. This way, if I overlook entering into any of those adjacent cells then those specific cells would be highlighted.

 

[Joe4]

That didn't quite work right. I don't think I explained it quite right. The only dependency should be on whether data is in A. Then look at each adjacent cell independently to highlight if it is left blank. No different than the 'highlight blank cells' basic rule, but do that only if something is in A. This way, if I overlook entering into any of those adjacent cells then those specific cells would be highlighted.

I still think what I posted does that.
What range exactly did you select before entering that formula?
And did you remove any previous Conditional Formatting that you may already have on these cells which could be interfering with it?

If it still does not work out, I think it would be very beneficial to visually post a small example of your data and exactly what you want to happen.
You know the old saying, "a picture says a thousand words".

 

[mbnott]

I still think what I posted does that.
What range exactly did you select before entering that formula?
And did you remove any previous Conditional Formatting that you may already have on these cells which could be interfering with it?

If it still does not work out, I think it would be very beneficial to visually post a small example of your data and exactly what you want to happen.
You know the old saying, "a picture says a thousand words".

I deleted the original 'highlight blank cells' rule, yes. The rule is exactly as you typed it, but I just changed the number from 1 to 2. What have I done wrong?

 

[Joe4]

The formula you enter has to exactly coincide with the range it is being applied to.
The formula always applies to the VERY first cell in your selected range.

If you select an ENTIRE column, row 1 will ALWAYS be the first row in that range.
So you need to write the formula as it pertains to row 1.

Otherwise, if you select the whole column, but write your formula looking at row 2, then row 1 of the conditional formatting will look at the values in row 2, and row 2 of the conditional formatting will look at the values in row 3, etc. So you will always be shifted off by one row.

So you have two options here if you want it to start in row 2:
1. Do NOT select the whole column. Select your specific range, starting with row 2.
2. If you want to select the entire column, you will need to write your formula to look at the values in row 1. But if you want to actually exclude row 1 from this (even though it is in your selected range, you can add an AND condition like this:

=AND($A1<>"",B1="",ROW()<>1)

so we are just adding one more condition that says that the row is not row 1.

Pick whichever method serves you best.

 

[mbnott]

The formula you enter has to exactly coincide with the range it is being applied to.
The formula always applies to the VERY first cell in your selected range.

If you select an ENTIRE column, row 1 will ALWAYS be the first row in that range.
So you need to write the formula as it pertains to row 1.

Otherwise, if you select the whole column, but write your formula looking at row 2, then row 1 of the conditional formatting will look at the values in row 2, and row 2 of the conditional formatting will look at the values in row 3, etc. So you will always be shifted off by one row.

So you have two options here if you want it to start in row 2:
1. Do NOT select the whole column. Select your specific range, starting with row 2.
2. If you want to select the entire column, you will need to write your formula to look at the values in row 1. But if you want to actually exclude row 1 from this (even though it is in your selected range, you can add an AND condition like this:

=AND($A1<>"",B1="",ROW()<>1)

so we are just adding one more condition that says that the row is not row 1.

Pick whichever method serves you best.

Ahh. Ok. I got it now. Thank you so much for all of your help! You made my day so much easier!

 

[Joe4]

You are welcome.

That is a very common mistake. People want to apply Conditional Formatting to entire column, except for a header row.
They select the entire column, but write the formula as it applies to row 2.
So now their Conditional Formatting is now shifted off one row!