1

I have a Table (as opposed to a Range) defined in Excel 2010, named Table1.

The table has blank rows separating groups of 3 to 5 rows with data. Note that not every column in every "non-blank" row contains data. However data exists in column B (named Table1[IdMap]) for every non-blank row.

I want to format the table (either using Conditional Formatting or static formatting) such that all of the all-blank rows have an off-white background color.

So far I've tried the following Conditional Formatting rules with no luck:

  1. Select Table1 (using Ctrl+A while the cell-marquee is in the table)
  2. Conditional Formatting > Rules > New Rule > Use a formula...
  3. "Format values where this formula is true":
    • =ISBLANK( $B:$B )
    • =ISBLANK( INDIRECT("Table1[IdMap]"))
    • =ISBLANK( INDIRECT("RC[-2]",0) )

2 Answers 2

1

I found the answer also on superuser: Using IF and ISBLANK statement to determine a range of cells if any of them have information in them.

Just use in conditional formatting =NOT(COUNTA($B:$B))

2
  • 3
    You actually don't need the IF, just =NOT(COUNTA($B:$B)) as you're just saying IF true, then true, else false. :)
    – Jonno
    Feb 25, 2016 at 8:49
  • I'll edit the answer :).
    – Michthan
    Feb 25, 2016 at 12:05
0

This formula worked for me: =ISBLANK(INDIRECT(ADDRESS(ROW(),2)))

  1. ROW() finds the current row number
  2. ADDRESS() returns the absolute address as text, in this case the current row, second column
  3. INDIRECT() converts the text given into a cell reference
  4. ISBLANK() checks if the cell is blank

So this formula will return TRUE for every cell in a row where column B is blank.

Steps

  1. Select your table range (using Ctrl+A while the cell-marquee is in the table)
  2. Conditional Formatting > New Rule
  3. Select Use a formula to determine which cells to format
  4. Under 'Format values where this formula is true:'
    • Enter =ISBLANK(INDIRECT(ADDRESS(ROW(),2)))
  5. Click Format to format your rule
  6. Click Ok and you're done!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .