How to conditional format based on multiple specific text in Excel

Question

I have a spreadsheet that i use to determine when what clients to contact when an issue arises  in the first workbook i insert a column every day and paste in information about any questionable habits from clients  including a client ID  unfortunately the data i am copying from also includes clients that are not to be contacted during an issue  i have a second page that has listed in column A all of the  dont check  client ids  is there a way to conditional format the original page to color any cells referencing a  dont check  client based upon the don t check listing in sheet 2   EDIT  there is more than just client ID in the first sheet cells  client id is just included  the format is   last file received date  Client Name  Client ID  Last X file received date   Last Y file received date   all in one cell   In theory what i would like is to go to conditional formatting  highlight cell rules  text that contains    then select A1-A45 on Sheet2 and click okay  obviously this is not possible  an error shows up stating  this type of reference cannot be used in a Conditional Formatting formula  Change the reference to a single cell  or use the reference with a worksheet function such as  SUM A1 E5   Thanks in advance

User · Accepted Answer

You can use MATCH for instance    Select the column from the first cell  for example cell A2 to cell A100 and insert a conditional formatting  using  New Rule     and the option to conditional format based on a formula  In the entry box  put    MATCH A2   Sheet2  A A  0   Pick the desired formatting  change the font to red or fill the cell background  etc  and click OK    MATCH takes the value A2 from your data table  looks into  Sheet2  A A and if there s an exact match  that s why there s a 0 at the end   then it ll return the row number   Note  Conditional formatting based on conditions from other sheets is available only on Excel 2010 onwards  If you re working on an earlier version  you might want to get the list of  Don t check  in the same sheet   EDIT  As per new information  you will have to use some reverse matching  Instead of the above formula  try    SUM IFERROR SEARCH  Sheet2   A 1  A 44  A2  0

User · Answer

Suppose your  Don t Check  list is on Sheet2 in cells A1 A100  say  and your current client IDs are in Sheet1 in Column A   What you would do is    Select the whole data table you want conditionally formatted in Sheet1 Click Conditional Formatting   New Rule   Use a Formula to determine which cells to format In the formula bar  type in  ISNUMBER MATCH  A1 Sheet2  A 1  A 100 0   and select how you want those rows formatted   And that should do the trick

[excel] How to conditional format based on multiple specific text in Excel

Examples related to excel

Examples related to list

Examples related to formatting

Examples related to conditional