Conditional Formatting to highlight one row based on *text* in other cells

**SeoulGal** · 04-23-2024, 03:52 AM

I've been trying to create a simple formula that will highlight cells in one row if some text appears in another cell. I've tried all sorts of variations but it simply will not work and yet I have other rules on my spreadsheet that are exactly the same and DO work. I'm at a total loss. I do wonder if the " on my keyboard is perhaps not recognised? But it looks the same on screen. So here is what I'm trying to achieve:

Highlight cells B41 - K41 in orange if cells Z2 or Z3 contain the words shipping label. There could be other text surround 'shipping label' but it's only those two words I'm looking for.

I started by highlighting cells B41-K41 ensuring that B41 is the active cell. I created a new rule with this formula: =SEARCH("*shipping label*",$Z$2:$Z$3). I've also tried various other options just on one cell (B41) such as =SEARCH("*shipping label*",Z2) and even with a MATCH but I cannot get it to work. I also applied the search to the entire spreadsheet in the hope it would find it somewhere but to no avail.

I have that exact formula elsewhere on the spreadsheet for different text and a different colour and it works perfectly every time so I'm stumped!

Please could someone tell me what I'm doing wrong?

**AliGW** · 04-23-2024, 04:00 AM

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**Pete_UK** · 04-23-2024, 04:14 AM

Might be easier to use COUNTIF in your Rule, like this:

=COUNTIF($Z$2:$Z$3,"*shipping label*")

Hope this helps.

Pete

**SeoulGal** · 04-23-2024, 06:19 AM

Update: it works! I changed where I was putting the shipping label note to a different cell just to start over and your solution now works! Thank you so much. I have no idea why the other one didn't as I have several variations of it already elsewhere on the spreadsheet and they all work perfectly. Thank you

**AliGW** · 04-23-2024, 06:26 AM

Could you please do as I have asked?

**AliGW** · 04-23-2024, 06:33 AM

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

**SeoulGal** · 04-23-2024, 06:39 AM

There was no need for a spreadsheet as it was just a simple formula.

**AliGW** · 04-23-2024, 06:41 AM

There might have been - and initially, you said that the simple formula wasn't working (which is what I was replying to - it could easily have been an issue with the actual data - extra spaces, etc. - it happens).

Please mark the thread as solved.

**SeoulGal** · 04-23-2024, 07:43 AM

I believe I have marked it as solved. I also clicked the reputation star next to the person who kindly provided the solution. Hopefully it's all updated now.

**Pete_UK** · 04-23-2024, 11:49 AM

Yes, thanks for the rep - I've been out all day.

Pete

Conditional Formatting to highlight one row based on text in other cells

LinkBack

Thread Tools

Rate This Thread

Display