Conditional formatting affected by the data filter

[billj]

hi everyone,

I used the formula below to apply a border line every 5 rows for my spreadsheet:

Please Login or Register  to view this content.

But I noticed that if a filter is applied such as when a category is selected, then it is no longer working properly. It seems that the formula cannot allow for filter.

Is there a better formula to allow it is still every 5 rows even when filter is applied?

[billj]

Found a thread which solves it:

[Aardigspook]

Try this as your CF formula:
=AND(EVEN(MOD((SUBTOTAL(3,$A$1:A1)),5))=4,MOD((SUBTOTAL(3,$A$1:A1)),5)+1=4)
That will highlight the same rows as your original formula, assuming that there is data in every row of column A. If not, change the refs to use a column which does have data in every row.

Edit: see you found another (similar) solution.