Function / VBA to avoid manually hidden cells (rows) for conditional formatting

Hello,

I have large spreadsheet that contains multiple data. Want to apply conditional formatting on the table so visually can segregate the various products for each line (sort A to Z on column D).
The function (column H) / conditional formatting rule work fine if the data is just sorted, but as soon as it's filtered (in this case want to be filtered by each line (column E), the function (column H) is calculating the hidden cells and the conditional formatting is wrong.

Is there any way this to be solved with function or maybe VBA so when the data is filtered / the rows manually hidden the function / condittional formatting to segregate the table?

Screenshot 2023-07-07 011046.jpg
Screenshot 2023-07-07 011156.jpg
Screenshot 2023-07-07 012032.jpg
Screenshot 2023-07-07 012112.jpg
Attached is the example spreadsheet.

Thank you in advance,
Nik