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).
- garbo7441Lv 72 years agoFavorite 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:
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.