Find and highlight numerical values one column based on a column of numbers in another worksheet.

Occasional Contributor

I have a column of ID numbers on several assignment tabs in a Workbook. I need to highlight the ID numbers in each of those columns whenever they match the ID numbers in one other column on a different Worksheet. The 'source' column is a list of the IDs that need to be flagged as priority on each of the worksheets where there is a matching ID. 


I want to use Conditional Formatting, but I can't figure out how to do the formula correctly to accomplish this. We are not allowed to use Macros in our environment. Thanks in advance for any help. 

8 Replies



Maybe with this rule for conditional formatting if the data is arranged like in the attached example.


This is the range the formatting is applied to. 

For some reason that's not working. Here is the formula I'm using. Is there something wrong with it?

=ISNUMBER(MATCH(A2,'239 Project Reps'!$K$2:$K$1949,FALSE)) 

This is where the 'priority list' resides


The range it is applied to for one of the worksheets is...



I add the $, but it automatically removes it when I hit Done.


It works perfectly in the attached file. In order to enter the conditional formatting in your sheet you can try as follows:

Select range A2:A11161 with the mouse.

Open conditional formatting -> New rule -> Use a formula to determine which cells to format -> Format values where this formula is true.

Then enter the formula and then select your format and click ok and ok.

If you then view conditional formatting -> manage rules, the applies to range shows:


The dollar signs of the "applies to" range were entered automatically by Excel.

I tried this suggestion. The $ signs do not appear. I can add them, but they go away. I should have mentioned that I'm accessing the Workbook through the Web. Not sure if that makes a difference, but the interface is a little different. I don't get any error messages, but for some reason, there is not formatting being applied.


Conditional formatting in Excel online is slightly different. For example no dollar signs have to be entered as shown in the attached file.

Thank you for trying. I'm at a loss for why it's not working. I've triple-checked everything. Not sure what's going on, but I guess I won't be able to do it that way. Is there perhaps another way?


An alternative would be to return TRUE or FALSE in the column next to the values in range A2:A11161. In the attached file i did this in column C. WAHR is TRUE in german and FLASCH is FALSE. As you can see all the values that are in range "239 Project Reps!$K$2:$K$1949" return WAHR (TRUE) in column C and are highlighted in column A. If the conditional format doesn't work you can use the TRUE/FALSE values in column C.