Excel 2010 VBA to set CF clears formatting of new inserted rows in Table Style

I have a mystery here and I hope some one can help me understand it, please.

I have a Table (with Table Style format) to which I have applied a CF to hide all rows not containing the value entered in cell H1. Actually, not really, it just turns the font to light gray to pretend to hide them. If the row has the value from H1, then those rows don't get their font grayed-out.


When I apply the CF and I only adjust the Font color, all works fine, as expected.


HOWEVER, when I apply the CF and I also adjust the FILL color (I set it to white), then some rows will be blanked out, when they shouldn't - the others are untouched. I have noticed that this happens if I insert new rows in the table. I would think not much of it if all rows would turn white either when nothing is typed in cell H1, or when it has something. Buy why, either way, some rows get filled in white with no logic of cause and effect. The other odd part of this mystery is that when I search for a one character string, then even some rows that do not have that one character will NOT be blanked out (or their font greyed out) when they should. For example, if you put the number 4 in H1, then the last row of the table will not be blanked out indicating that it has the character, but it does not.

Why does this happen?

This is the CF formula I use when applying it via the Conditional Formatting Manager:

CF Formatting: Font gray RGB(217, 217, 217) and Fill white RGB(255, 255, 255) for the range A4:Z28

Please Login or Register  to view this content.

Here is the VBA I am using:

Please Login or Register  to view this content.

I do need to use the CF via a VBA though.


Thank you so much in advance.