Apr 18 2022 08:01 AM
Apr 18 2022 08:01 AM
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.
Apr 18 2022 08:17 AM
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.
Apr 18 2022 11:52 AM - edited Apr 18 2022 11:55 AM
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.
Apr 18 2022 12:31 PM
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.
Apr 18 2022 12:42 PM
Apr 18 2022 01:24 PM
Conditional formatting in Excel online is slightly different. For example no dollar signs have to be entered as shown in the attached file.
Apr 18 2022 01:44 PM
Apr 18 2022 01:54 PM
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.