Search code examples
excelexcel-formulaexcel-2013conditional-formattingexcel-indirect

Conditional Formatting for filtered area

I currently use the following formula in conditional formatting to highlight ever other row.

=IF(ISBLANK(A1),"",MOD(ROW(),2)=0)

However I would like to find a way to modify this to only apply to currently visible section of a filtered dataset.

So for instance the above currently highlights every even row but if I filter the data set to only show rows: 2,5,7,9,10,12,15. I would want it to highlight only rows 5,9,12 or 2,7,10,15.

I am also wanting to avoid using macros I could write this into a macro but the end user of this file is unable to run macros due to their IT department not trusting macros of any sort.

I am using Excel 2013.

Solution

  • Please try HOME > Conditional Formatting, New Rule..., Use a formula to determine which cells to format, Format values where this formula is true::

    =ISODD(SUBTOTAL(3,INDIRECT("A$1:A"&ROW())))

    Format..., select formatting of choice, OK, OK.