I have nearly the same problem like in this thread and tried its solution:
www.mrexcel.com
I have two Sheets. On the first sheet ("Query" )I have a column where I want to format a word if this word exists anywhere on the second sheet ("Synonyme").
My formula looks like:
I get an error:
Formula or value in conditional formatting is invalid
This works but uses wildcards (what I don't want):

Don’t use * as a wildcard
I have a countif statement that I use to find all exam grades that are A*. I store A* in a cell and use that cell in the countif. Problem is it picks up everything starting with A as it’s treating the * as a wildcard. Is there a way round this?
I have two Sheets. On the first sheet ("Query" )I have a column where I want to format a word if this word exists anywhere on the second sheet ("Synonyme").
My formula looks like:

=COUNTIF(Synonyme!A1:AM146;SUBSTITUTE(B2,"*","~*"))>0
I get an error:
Formula or value in conditional formatting is invalid
This works but uses wildcards (what I don't want):
=COUNTIF(Synonyme!A1:AM146;B2)>0