Monday, April 23, 2012

Highlight First Blank Cell With Conditional Formatting

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:
  • Vertical data entry:   =ROW(A1)=COUNTA(A:A)+1
  • Horizontal data entry:   =COLUMN(A1)=COUNTA(1:1)+1
If there are empty cells before the actual input range, you need to modify the formula. Increase the "+1" with every empty cell above (or to the left of) the actual input range. This type of conditional formatting can be applied on the entire column, entire row, or custom range, and there would only be one highlighted cell at a time.

Excel Geek insiders will get the sample file from Eric.