Conditional formatting stop if true checkbox not working, need alternative

Question

I have a spreadsheet that I need to track expiration dates and needs. I have a conditional formatting formula to allow text prior to the date so that the CF will look at the date and change the cell color for 30/60/expired. I have a conditional formatting rule set up to change the cell to purple if the cell begins with the word Need. However, when I put the words NeedBy in front of the date, it also changes to purple. I thought of using the word DueBy instead of NeedBy but that isn't always going to be the case and won't make sense.

I'm already using a . before text so I can't use that.

I've tried putting a space after the word Need in the conditional formatting and that doesn't work and it's not sustainable.

I've tried changing the rule to look at cell value ends with and contains but that doesn't work either.

Answer 1

Select the cells that you want to format.

On the home tab of the ribbon, click Conditional Formatting > Manage Rules...

Make sure that the rule for NeedBy is listed above the rules for Need - rules are evaluated from top to bottom.

You can use the arrows in the Manage Rules dialog to move rules up or down.

Answer 2

Didn't work. I may have to live with the purple "NeedsBy" or find another word or symbol to use at the front. I changed the shade of purple and text to black so it would at least match the rest of the sheet.

This is my CF right now for one table. My CF ALWAYS looks like this. It was nice and clean when I created. Once we start adding/deleting rows/columns, my CF begins looking more like this. I go in once a quarter or so to redo all the CF's on all 9 worksheets on one workbook. Each sheet is has a table format applied. Each sheet has the same original CF's applied. Any guidance to avoid this would be helpful.

AND if I'm on one worksheet and delete one CF, it deletes that CF for ALL 8 tables. So I have no choice but to clear CF for the one cell and format painter from another cell.