Conditional Formatting through VBA

Hello Team

On worksheet name "XX",

Data is arranged like -

Column H has basic numeric data
Column I used for Filter purpose
Column J derives same value from corresponding cells in Column H using following formula
Cell J2 =

=SUBTOTAL(9,H2)

Further, Column J is conditionally formatted. I manually apply following formula (because, through VBA every day, old data is deleted, Coditional formatting is lost since new data is populated).

Please Login or Register  to view this content.

Good if $J$500 could be changed to dynamic formula, like till last filled cell in Column J, since ROW data keeps changing, currently, filled data is upto 188 only

How Can I do it through VBA / Macro code observing
1) Deleteing erstwhile applied conditional formatting on entire Column J
2) Make use of above Conditional Formatting formula, and if true, apply Conditonal Formatting
3) Result should work, considering Excel Wrokbook is in base 2010 version.

Sample workbook attached.

Try

Set "Control" as dynamic range

=OFFSET(XX!$J$2,,,COUNTIF(XX!$J$2:$J$20000,"<>"),1)

and change CF

=COUNTIF(Control,"<"&$J2)<$P$1

Applies To: =Control