locked
VBA -Color Words & Wildcards From Excel RRS feed

  • 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 :)

    Thursday, January 19, 2017 6:10 PM

Answers

  • 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)


    Thursday, January 19, 2017 9:07 PM

All replies

  • 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)


    Thursday, January 19, 2017 9:07 PM
  • 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 :)

    Thursday, January 19, 2017 9:23 PM