Excel function determine if a cell is visible
Instead of subtotal using a sum on another column, you can use subtotal using counta
to see if a (known-non-blank) cell is hidden or not. For example, if column A
will normally be visible (unless the row is hidden)
= IF( SUBTOTAL(103,A2)=1, "VISIBLE", "HIDDEN (or blank)" )
You can put this formula in a column that may be hidden, and it will still work.
In conditional formatting, then, you can just use: = SUBTOTAL(103,$A2)=1
to determine if the row is visible.
As an addendum to Phrogz's answer, if you need to check whether a cell in a column is hidden, try either of the following,
Conditional Formatting
=CELL("width",TargetCell)=0
This updates automatically as soon as a column is hidden.
Formula Check
=IF(CELL("width",TargetCell)=0, "Cell is hidden.", "Cell is visible.")
This formula will not update automatically and you would have to direct Excel to "Calculate Now" by choosing the menu option or pressing "F9".