This is going to be a long one…
2 things to note:
1. There is nothing special about rng. It's an invention of mine, nothing more than a sequence of letters. I've used it to prefix a longer name, because it reminds me that it's a range.
2. Your lookup table (the table called MKInventory on the Inventory sheet) is a proper Excel table, which is good because any formulae referring to it will look at the whole table extent, and not need to be adjusted when the table has extra rows added to it (or taken away). You don't want to have to go into conditional formatting and cahnge the formula every time you change the size of the lookup table.
So, in the attached, let's first look at Sheet1, where the lookup table is not a table.
First a few explanations:
I've put the lookup table on the same sheet (we can see what's going on).
I've used a smaller (bare bones) version of what you're wanting to achieve.
Next, in columns N:W you'll see several grids the same size as where you want the conditional formatting (CF) to apply (B2:K7). CF works on TRUE/FALSE, where TRUE means apply the formatting and FALSE means don't. These grids show where CF will apply.
The first 3 grids show the development of the CF formula, with the formula in cell N26 being the formulae above consolidated into one, ready to use directly in CF.
On this Sheet1, the lookup table is not proper Excel table, and the actual CF for cells B2:K7 refer to plain cell adresses. You'll see that if you add rows to the lookup table, that those extra rows are not used in any other formulae, neither on the sheet nor in the CF of the sheet.
That's it for Sheet1.
On to Sheet1 (2).
Here the lookup table is a proper Excel table called Table1.
You'll see the formula in cell N10 refers to Table1, so does the formula in N34.
Cell N26 still refers to cell addresses; more on that one later.
Cell N34, referring to Table1, works perfectly on the sheet. However, if you try to use it in CF, the CF dialogue box refuses to accept it. Why? I don't know - it would be so much simpler if it did, but Microsoft says No.
So to get around that, create a named range, called anything you choose, which encompasses the same range as Table1 (more specifically, the databody, not the headers). On that sheet I called that range Anyting (you select the range, and type Anyting into the Name Box which is at the extreme left of the formula bar). I then used that name in the formula in cell N42. Try to use that formula in CF and all works well.
If you take a look at the Name Manager, you'll see that Anyting refers directly to Table1; and because Table1 adjusts its size with added rows automatically, so does Anyting.
From all this, you see that if you refer to a named range in CF, it has to exist in Name manager.
Now for the surprise (for me): back to cell N26.
While exploring I found that even if I used cell addresses to refer to the lookup table, these cell addresses adjusted themselves to the size of the Table1! Not only that, but so did the references in CF! It must be something to do with Excel realising that those cell addresses refer to a proper Excel table. I still wouldn't like to use it, as it might get out of sync; if ever the cell references do not refer to a whole column of a table or a whole table, they cease to self-adjust, so personally, I'd stick with giving the table a Name and using that in CF.
