Log In
Register
Home
Active Member
May 26, 2020
Offline
Hi,
I have a customer feedback data in "comments sheet" in column B and i want to color the keyword that are in "Keywords" sheet (3 columns with different keywords categories about the service:- Good, bad and neutral keywords 40-50 rows), like Good keywords in Green, Bad in Red and Neutral in Amber.
Can you help me with the code, that can find the those keywords and change their font color as per above.?
Please find the attached file, I've only included few comments and keywords.(there are many more)
I've highlighted the keyword on "Comments" sheet that are matching from my "Keywords" sheet in a color as per the keyword category it belongs (expected Output)
Example:-
comments sheet cell B2-
"Like", "company" in GREEN because both keywords are under GOOD category, in my "keywords" sheet cell A2, A3
"Rude", "Tone" and "attitude" in RED, because all 3 keywords are under BAD category, in my "keywords" sheet cell B2, B7, B10
"advised" in AMBER, because the keyword is under NEUTRAL category, in my "keywords" sheet cell C3.
Let me know if you have any question with this query.
I really appreciate you quick response and help!
Thanks in advance!!
Maggie
October 5, 2010
Offline
Hi Maggie,
See attached.
To run the code, click into a cell where the text is that you want to check e.g. A1 then click the blue button 'Color Words'.
Regards
Phil
Active Member
May 26, 2020
Offline
Hi Phil,
Instead of hard coding the keywords and colors, can we do some like.
If a keyword is presented in a Column A then color it Green, if its in column B then Red color and if its from C color it Orange.
This will be much helpful if i want to add more keywords, or change some keywords, i'll simply add in the range and change the code.
so, can we change the keywords by defining a column range as an array, and specifying color if its from a particular array?
something of this sort:-
Good = Sheets("Keyword").Range("A1:A11").Value
Bad = Sheets("Keyword").Range("B1:B11").Value
Neutral = Sheets("Keyword").Range("C1:C9").Value
MyWords = VBA.Array(Good, Bad, Neutral)
MyColors = VBA.Array(vbGreen, vbRed, mothOrange)
please help!
Thanks,
Maggie
Active Member
May 26, 2020
Offline
Phil, its only changing the color of "time" and showing me a "Type mismatch" error.
Attaching the data file.
Comments sheet- is the raw data, i want to color basis the keywords in the next sheet as per their type.(as above)
please run it at your end and advise me.
Thanks!
Maggie
October 5, 2010
Offline
Hi Maggie,
I was working on this update (attached) and as I go to post it I see your latest message.
Please try using this new version to see if it overcomes your issue.
It uses 3 tables which you'll find on the Keywords sheet. Everything is dynamic so add/remove words to these tables as you wish.
Regards
Phil
1 Guest(s)
