Failing FIND method?

Hello guys.

I have a scenario where i need to set the interiour color of all cells in a sheet according to it's values.
The correct color is stored in a list on another sheet and the value that represent the color is loaded into an array along with the colors long value.

It works pretty well, except it won't set the color on cells that contain a formula instead of a value (despite lookin:=xlValues).
Can you spot what might be wrong at the first glance of would you like me to attach the xlsx file?
I don't understand why the code keeps ignoring it, despite that the fact that the FIND method is indeed finding the value.

Please Login or Register  to view this content.

Thats it!

Well, almost.
Making the first change you suggested would cause the code to run very slow (had to break).

But removing the entire WITH statement did the trick!
Thanks a lot for getting me on the right track.

Actually..
I realized this code is backwards.

The way its written now, it loads 257 variables into an array and then it searches each and every cell in the range for a match from those 257 variables.
When i first wrote this, it was 16 variables, but now the code wont work anymore (it takes forever).
Is there a way to do the other way around?
Check every cell in range to see it the value in that cell exists in the array?
Or as an alternative skip cells that are empty?
The range should really be dynamic, but it can grow horizontally or vertically (or both) so i'm not sure which method to use last cell by rows of colums? Or it doesn't matter?

Please Login or Register  to view this content.

Too hard without seeing your workbook.

Completely untested.

Please Login or Register  to view this content.

I understand it's rather hard to re-invent it just based on this. The "Colors" sheet is just a two column list and all "color identifiers" are max 3 chars.
IŽll try your code out..

Please Login or Register  to view this content.

Well, that quickly went out or range. ^^

I really appreciate your time. If you feel up for it, i've uploaded my WB on dropbox. (didn't manage to open the attach dialogue on this forum correctly)
https://www.dropbox.com/s/behlyum1ny...ning.xlsm?dl=0

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

One more change
Insert one line

Please Login or Register  to view this content.

Ah, that's right, or it will fail the regex when the case differs.
I never though of using regex here, but your solution is much better than mine.
It still takes a while if the area is huge but atleast it's doable.

Thank you so much jindon! <3