Excel - Complex Conditional Formatting question - Formatting based on part of cell contents. Is it possible? How can I do it?

Is there a way to use conditional formatting to highlight cells that contain but are not necessarily an exact match of the contents of Cell a particular cell?

For example: If Cell $C$4="ODS" then any cell in the range C4:C53 that includes "ODS" would be highlighted (AODS, BYODS, ODSXS, ODS). Anything in that range that doesn't contain "ODS" would not be highlighted (OD-S, BDS, XYZ).

1 Answer

Relevance
  • 2 years ago
    Favorite Answer

    Select cells C4:C53.

    Access conditional formatting and select New Rule

    Select 'Use a formula to determine which cells to format'.

    In the 'Format values where this formula is true' textbox, enter this formula:

    =ISNUMBER(SEARCH("ODS",C4))

    Select 'Format', select the Fill tab, choose the color to use for formatting, and click 'OK' twice.

    Note: Excel will automatically adjust the cell reference in the formula for each cell in the range. The SEARCH function is not case sensitive and will parse 'ODS', 'ods', 'Ods', and even 'OdS' as a valid match. If you wish to make it case specific, use the FIND function, instead of SEARCH.

    • atomzer0
      Lv 5
      2 years agoReport

      I had to tweak it a bit, but you set me in the right direction. Thanks a lot.
      This is what I ended up with =ISNUMBER(SEARCH(CONCATENATE("*",$C$1,"*"),$C4))

Still have questions? Get your answers by asking now.