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

@Marcus_Booth 

=ISNUMBER(MATCH(A2,Tabelle1!$A$2:$A$23,FALSE))

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

=$A$2:$A$16;$D$2:$D$11

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...

A2:A11161

 

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

@Marcus_Booth 

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:

=$A$2:$A$11161

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.

@Marcus_Booth 

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?

@Marcus_Booth 

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.