Hey, just wanted to share a quick trick about conditional formatting.
If you have a data entry sheet with input in adjacent cells within the same row or column, and you want the next blank input cell to be highlighted with a color, you can apply conditional formatting based on a formula.
The following formulas assume that your input is starting from cell A1 and all the input is in adjacent cells:
Excel Geek insiders will get the sample file from Eric.
If you have a data entry sheet with input in adjacent cells within the same row or column, and you want the next blank input cell to be highlighted with a color, you can apply conditional formatting based on a formula.
The following formulas assume that your input is starting from cell A1 and all the input is in adjacent cells:
- Vertical data entry: =ROW(A1)=COUNTA(A:A)+1
- Horizontal data entry: =COLUMN(A1)=COUNTA(1:1)+1
Excel Geek insiders will get the sample file from Eric.
3 comments:
I had to adjust your formula to =COLUMN(A1)=COUNTA(1:1) to get the desired results.
I actually prefer to use =AND(A$1<>"",B$1="") which gives the desired results as well as highlighting empty cells not at the end of the range in yellow.
The goal of the formula was to highlight the next empty cell within a consecutive input of adjacent cells. For other specific scenarios the formulas can be adjusted to fit the need.
Your alternative A&B formula is not highlighting empty cells anywhere, it's always highlighting non-blank cells.
That's a very creative tip, well done!
Post a Comment