VBA -Color Words & Wildcards From Excel

Question

Hi friends,

 

I am trying to color some words I have made a list in excel and it has some wild cards

 

In Column A it searches for the wildcards and then applies the rgb colur in column B

 

Now it works - but it is skipping some colors and not coloring correctly and I have tried for days to fix this.

 

Why is it skipping some colors - is it the wildcards.

 

I have attached 2 books please advise

 

https://1drv.ms/w/s!AvcJQhgRih3LklYDaSGk4KBgee5f

 

https://1drv.ms/x/s!AvcJQhgRih3LklW1-7NZ6VDhXvV7

 

 

 

Please advise thank you

---

Cheers Dan :)

Answer 1

When searching with MatchWildcards = True, [nb] doesn't mean the string "nb" but "either an n or a b".

So "\#[nb][A-z]{1,}" means: # followed by either an n or a b, followed by a number of characters between A and z i.e. including all upper case and all lower case letters, and also [ \ ] ^ _ and `since these characters are between Z and a in the ASCII/ANSI character set.

#boVBA, #blVBA, and #nbVBA all match this pattern, which is not what you wanted, I suspect. Try changing the pattern \#[bo][a-z]{1,} to \#bo[a-z]{1,} and \#[bl][A-z]{1,} to \#bl[A-z]{1,} etc. etc.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Hello Hans,

 

This has been a most annoying task  - I tried almost everything bar chucking my  tea at the laptop.

 

I followed the wildcard search manual over and over untill nothing  :(

 

I found some wildcards for text extraction on my research and then i thought i could make some colors of words - thats what got me into trouble in the first place :)

 

 I guess that’s solved now

Thank you for helping

Have a great evening :)

 

 

---

Cheers Dan :)