VBA code to fill cell color based on Cell Value

[a1s2d3f4]

I am trying to create a quick automated way to color a range of cells in my worksheet based on their value (string).
(First text sub-string in the cell, up to a defined separator - e.g. "Germany;" for a cell containing "Germany;France;Italy;" - would get a fill color I defined for "Germany;" in a Lookup table (e.g. "blue").

I tried doing it via Conditional Formatting and got the results I wanted, but the recalculating it was doing every time I edited a cell made this solution impractical for me.


So, I looked into doing it using VBA code and mocked something up as a test:

Please Login or Register  to view this content.

I couldn't believe how slow this was by comparison (I waited at least a minute before it was over).

Isn't VBA supposed to be the fastest way to get those things done, and if so, what's wrong with my code?


Thanks

[nilem]

maybe something like this

Please Login or Register  to view this content.

[a1s2d3f4]

Now, that is very fast - thank you.
This "With Range" trick seems a lot better than For...Next.
Or maybe the main trick is to turn off ScreenUpdating?

I have more parts to this problem, but I will post them in separate threads.

[a1s2d3f4]

Actually, I should probably ask here again, because of my table issue.

Because I have a large number of "sub-strings" (a lookup table that contains maybe 150 items, with corresponding colors to set) to search for in the B2:AAA255 range, following your .Find solution I would need to iterate through the items in that lookup table, and use .Find each time on my entire range. That seems to be less efficient than looking at every cell in the B2:AAA255 range once and finding the corresponding item in the lookup table. In other words, .Find seems to guarantee that for every one of my 150 items in a table we'll be walking through all the cells in the B2:AAA255 range, whereas, if we were to use VBA's lookup function, we would likely be finding the corresponding item sooner than that.

Am I thinking about it right? (In which case, can this be coded alternatively)?

I did, incidentally, add Application.ScreenUpdating = False line to my initial code, and while it did speed things up somewhat, it was still very very slow compared to your solution.

[nilem]

could you show an example of your file?

[a1s2d3f4]

testcolor.xlsx

Ok, I attached a mockup called testcolor.xlsx.

The first sheet contains a (currently shorter) lookup table with color names and RGB values. I haven't yet figured out how to encode my gradients. (I'll probably need two colors and a gradient type column).

Also, I had another crazy idea that seems like it would be the most efficient.
Subtracting ranges.

With your code, you grab the entire range "B2:AAA255", then you create "sub range" r which holds all the items that correspond to s. What if at that point I were to "subtract" r from B2:AAA255, giving me a reduced range to search with the next s value? (like if I were to walk through my lookup table and first find all my "Belgium;" cells, then immediately remove those cells from the "large range" and next time not take up cycles searching through those again).

I just can't seem to find information on whether excel can manipulate ranges that way or not.
EDIT: http://www.dailydoseofexcel.com/arch...-and-subtract/ seems to be related to what I am talking about

[nilem]

see attachment

[a1s2d3f4]

Hey, there.
I somehow lost touch with this thread and just re-visited it today. Your solution is amazing - wow.
Thanks - I am still studying the code to see what you did. Спасибо.