exceloffice365

Highlight cell in first column if row contains a partial text match


I would like to highlight cells in the first column if there is a partial string match in the same row. The spreadsheet has 100 rows and 100 columns. I would like this applied to the majority of the spreadsheet except a couple header rows.

I would like to be able to determine if any of the cells in that row contain the word "missing" and then highlight (fill) the first column in that row. I would also like this to be case insensitive. I am using Microsoft Office 365 online if that makes any difference.

For example I would want cell A18 highlighted if BB18 was " 0.0 Missing Done 1 ".


Solution

  • You can use the conditional formatting feature. Select the first column and command "Conditional Formatting-->New Rule...":

    enter image description here

    The formula you need can look like (for the active cell in the row #2 as depicted, otherwise adjust the row number in the formula)

    =OR(ISNUMBER(SEARCH("missing",B2:K2)))