Table Banding - value dependant

[shippey121]

Morning All

i had a table where i use row banding, quite simple odd rows white, even rows grey. the table got more complex when some rows had multiple lines that needed to be banded together

what im wanting to do is alter the banding depending on a cell value, i found

MOD(SUMPRODUCT(--($E$1:$E1<>$E$2:$E2)),2)

this check column E values and everytime there is a change using conditional formatting switches the colour
row Column E Column F
Row 2 CH59690 Rich
Row 3 CH59690 Rich
Row 4 CH59691 Craig
Row 5 CH59691 Craig
Row 6 CH59692 Rich
Row 7 CH59692 Rich
using the data above row 2&3 should be gray, rows 4&5 should be white. 6&7 back to gray. using the formula above works fine.

however if i put a filter in place on column F, when i filter "Rich" rows 4&5 disappear, rows 2,3,6&7 stay gray due to the formula seeing the hidden rows, i would like them to be Rows 2,3 Gray, 6,7 White

does anyone have any solutions / ideas on how this can be achieved either formula or VBA

 

[Eric W]

Try this rule:

=MOD(SUMPRODUCT(--($E$1:$E1<>$E$2:$E2),SUBTOTAL(103,OFFSET($E$1,ROW($E$1:$E1)-ROW($E$1),0))),2)

 

[shippey121]

almost

seems to not work when i gets to a blank ref, i can alter sheet so there are no blanks
ill have a play about
thanks

 

[Eric W]

That's true. SUBTOTAL is one of the very few functions that can tell if a row is hidden or not. But it doesn't only detect if it's hidden, it has to do another function first. You can check the documentation for what functions it has available, but about the only one that works in this case is COUNTA. So the SUBTOTAL uses OFFSET to check each cell in the range to see if it has a value in it or not. And if it's hidden, it will be zero. But if it's not hidden but empty, it will still be zero.

So you could make sure that all the cells in your column have something in them, or if you have another column that always has data in it, you could just change the column reference the OFFSET refers to. If that column is A, change the $E$1 right after OFFSET to $A$1. You can change the rest after that if you want, but that's the only one you have to.

 

[shippey121]

you a a star....works perfect,
i do have a column that does always have to have data so switched it to that

thanks

 

[Eric W]

You're welcome.