Highlight blank cells if data present within row

Closed
Smokeydriver - May 23, 2018 at 05:36 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- May 24, 2018 at 11:38 AM
Hello,

So I know it's using conditional formatting but I can't find a formula for what I want. I only want the blank cells highlighted, not the entire row. And I want this to happen only if there is data in any cell is that row.

Example- You have a list of student info. You want to easily see which info hasn't yet been entered quickly so you want blank cells highlighted. BUT...you don't want cells to only change when data is entered because the whole screen would be highlighted at first which looks horrible.

Thanks!!

1 reply

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Updated on May 24, 2018 at 11:39 AM
Hi Smokeydriver,

How about this formula:
=AND($A1<>"",A1="")
And then apply to whichever rows you want:
=$1:$20

- When you test this on a blank row you notice non of the cells are colored.
- When you put something in column A, the rest of the corresponding row will be colored.
- The color of a single cell within that row will dissapear once you put something in that cell.


It would make more sense to me not to color the entire row. I mean, you probably won't fill 16.384 columns with data, right?
So consider the option to add column letters to the applied range:
=A$1:K$20

Best regards,
Trowa
0