highlight certain cells based on text in different cell

[HarleyRider]

So, I have a billing spreadsheet with a list of names in column E. I have pay columns that are based on years of experience. The names in Column E contain an * if they are first year and ** if they are 2nd year. What I want to do is change the background colors depending on * or **. I've tried with conditional formatting but what I've tried isn't working. So, if cell E5 has a name: last name, first name (*) then I want cell H5 to turn blue. Can conditional formatting do this alone or do I need a formula to assist? Thanks in advance.

[FDibbins]

If you have name name (*) (with parentheses) then yes CF alone can do this for you, and yes you will need a formula (in CF to do it for you.

[HarleyRider]

Any idea what my formula would be to make this happen?

[HarleyRider]

Ok. I think I've figured out a formula for a single cell. I'm using =ISNUMBER(SEARCH("*",E5))

Now, I'm struggline to apply this to a group. So, cells E4-E128 contain the names. Column H (cells H4-H128 contain a number.
I want the cells in column H to only turn blue if the cells in column E contain a (*) So, now how do I apply the conditional
formatting to a range of cells. When I highlight the cells and enter the formula in conditional formatting and set the color,
it doesn't work.

[jeffreybrown]

You need to apply the conditional format to the range

Conditional Formatting

• Highlight applicable range >> H4-H128
• Home Tab >> Styles >> Conditional Formatting >> New Rule
• Select a Rule Type: Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =ISNUMBER(SEARCH("*",E5))
• Format… [Number, Font, Border, Fill]
• OK >> OK

Note: When you apply conditional formatting, you must select the first cell in the range to the end. You cannot select H128 and highlight up to the H4.

[HarleyRider]

that's perfect. Now, Is there a way to apply it to mulitple columns, H, P, X and AF? When I highlight the cells 4-128 in those columns, it only applies the formatting to the last column selected and not the other 3.

[jeffreybrown]

Is the formula the same for all columns? Are all columns going to reference E?

[jeffreybrown]

If all those columns are going to evaluate against column E, then is the Applies to box >> =$H$4:$H$128,$P$4:$P$128,$X$4:$X$128,$AF$4:$AF$128

Also, your formula should actually be =ISNUMBER(SEARCH("~*",$E4)) or else everything will be true.

There is a significant difference between using the asterisk and the asterisk preceded by the ~.

The asterisk is a wild card and in this case means anything in the cell. Instead we place the ~ in front of the asterisk which tells Excel to look for the specific asterisk character.

~ = Tilde

[HarleyRider]

Thank you very much for the help and getting me going in the right direction. I was able to get it solved entirely. Thanks again.

[jeffreybrown]

You are very welcome and thanks for the feedback!