Excel VBA Color cells inside a table that contains value

Hello everyone.
First of all, I’m a VBA rookie, so please bear with me
This is what I’m trying to do:
I have one sheet (Sales) with a table (Table1) that looks like this.
Sales.jpg

What I’m trying to achieve is to set a specific background color to every third row, except for cells that don’t have a value.
The background color is based on the brand name (in column 2). The color for each brand is stipulated from another sheet (Colors) that looks like this:

Colors.jpg

I found this code online that colors every third row as I want. But as you can see in the code it colors the whole row, so I added a macro to clear
all the cells inside the table without a value.
But it’s really slow and gets slower for every brand that’s added to the table.

I guess there’s a much better and smarter way to do this, but it’s beyond my knowledge
and I hope I can get some help and guidance to make the script work better
Thanks!

Please Login or Register  to view this content.

Hi Rob

Why don't you use conditional formatting, much better than vb coding.

Try this code, you don't need to run the clear empty cells code, so it should run faster

Please Login or Register  to view this content.

Hi Ramirez.
The challenge is that the table contain over 50 brands right now, and new brand will be added frequently.
So I would prefer to just be able to add a Brand and Color to the list, instead of having to alter the code every time a brand is added.

Hi maniacb.
Thanks for the response.
I also like the sales numbers to be colored in every third row, but if I extend the offset to cover the sales number, the empty also gets colored, is there’s away round that?

Hi Rob

For this request of yours i would still go for conditional formatting. When you make any entry in column B then the row will be highlighted.