VBA Excel - Conditional Formatting in Table based on group

[JILBO]

Hi All,

Trying to find some VBA to conditional format within a table (Table1)

So for every change in value in the [OP] column i want the formatting to alternate between 'Non' and 'Grey/Bold Red Text' accross all columns in (Table1).

Image below for reference

Thanks


28703

[Bob Phillips]

Why do you want VBA? You can do it with CF and a simple formula

=ISEVEN(SUMPRODUCT(1/COUNTIF($B$2:$B2,$B$2:$B2)))

and set the fill colour.

[JILBO]

Hi Bob, thanks for suggesting this. The concern is there are typically many 1000's of records and i didn't want a formula slowing anything down (which this seems to do) but this doesn't use a helper column which is good!.

Can it be done in VBA though?

[SamT]

Even with VBA using Arrays and ScreenUpdating = false... First after any change, the table must be sorted then recolored starting from the original Row the change happened in.

Depending on which Row was changed it could take anywhere from a few milliseconds to a minute. The algorithm for the Reformatting sub is going to be pretty complex.