Dynamic Row Shading with Conditional Formatting Fo

Question

I have a document with functionality via data validation selection. Each selection triggers a macro to hide certain rows. I have the following conditional formatting formula =MOD(ROW(),2)=1 and it works great when all rows are unhidden, however I'm looking for a more dynamic formula that can change when the rows are automatically hidden. I'm open to using VBA in lieu of conditional formatting.

Answer 1

You can use SUBTOTAL to count non-hidden rows. This relies on a column being filled (ie no blanks). I've used column A, use a different one if you need to.

Conditional Format formula

=MOD(SUBTOTAL(103,A1:$A$1),2)=1