Conditional format and hidden / filtered rows

[cjhawk]

Hoping someone can help. I have searched and played with this for weeks now.

I have a conditional format that I want to change if I hide rows.



As you can see the "True" value remains the same if I hide rows.

I am not after alternate 'zebra' shading. Which I have achieved via the subtotal formula. I am after shading via category. If I want to hide/filter a category for whatever reason I want the shading to remain alternate per category.

I would prefer not to use VBA - but if it comes to that well.........

Thank you in advance.

 

[NARAYANK991]

Hi ,

If you want a VBA solution , see if this works ; test it thoroughly.

Narayan

 

[Chihiro]

Here's Formula method.

You need 1 additional helper column.

In D1: =SUBTOTAL(103,A1)
Copy down.

Then modify C2 formula to...
=IF(AND(A2<>A1,D1=1),NOT(C1),C1)

However, this will fail when you filter out last/first item in category using Manufacturer column.

It will work as long as filter is based on category only.

I'd imagine you can use Match or other function to get around it, but haven't got the time right now to explore.

 

[cjhawk]

Here's Formula method.

You need 1 additional helper column.

In D1: =SUBTOTAL(103,A1)
Copy down.

Then modify C2 formula to...
=IF(AND(A2<>A1,D1=1),NOT(C1),C1)

However, this will fail when you filter out last/first item in category using Manufacturer column.

It will work as long as filter is based on category only.

I'd imagine you can use Match or other function to get around it, but haven't got the time right now to explore.

That is fantastic. I have not used the AND formula before. Got to love learning something new.

Thank you very much.

 

[NARAYANK991]

Hi ,

See if this works.

Narayan