Conditionally formatting a cell only if another cell contains a specific text value.

[m_gron48]

I have a linked table in Excel that brings in trade details with many columns. Each row is a different trade and the columns are the details of the trade: Buy or Sell, price, trader, quantity, delivery date, location, etc. When I refresh the table, new trades flood into this table if they fail to meet certain criteria that I've written in VBA. When a trade fails said criteria, there is a short explanatory comment that tells me why the trade failed the criteria and which detail it pertains to. This comment updates to the column titled COMMENT as ";AAAAA". For example, If the price was $105 instead of $1.05, the comment, ";PRC", will update to this column.

Since there are many columns to this table and many criteria that a trade could potentially fail, I want to be able to see what the comment says, scroll to the right and see the value that caused the trade to fail the criteria right away instead of searching through all of the columns for the one labeled, "PRICE" and then looking down to see what the value is for that specific trade.

So, if trade #1 contains ";PRC" in the "COMMENT" field, I want only the cell that contains trade #1's Price to highlight a certain color.

The catch to this is, however, one trade can have multiple comments in the "COMMENT" field. It can have ";PRC;QUANTITY" if the trade failed both price criteria and quantity criteria. I want it then to be able to highlight the PRICE cell as well as the QUANTITY cell.

I know I'll have to use a conditional formatting formula, and I know I will have to make a different formula for each potential comment, I just can't figure out what formula I need to use.

Below is an image of what I want, but with a lot less columns.

Can anyone assist?

[Special-K]

Use

=ISNUMBER(SEARCH(...))

A successful SEARCH returns the position of the searched word, hence the ISNUMBER.
A failed SEARCH returns an error, ie not a number.

[m_gron48]

Thanks! I believe that worked!

[m_gron48]

I've been having some issues with the conditional formatting that I created for this table. Like I said, it's for a linked table. I refresh this table everyday. The formatting worked at first but now whenever I refresh my table, the formatting does not show up.

Do you know if there is a way for me to change this?