Забыли вложение

Вроде верно, проверьте на данных.
Если вам помогло такое решение, советую разобраться с условным форматированием с применением формул.

1. Копируете нужные данные
2. Выбираете ячейку, куда нужно вставить -> правая кнопка мыши -> Специальная вставка -> Значения -> ок

Поясню. Подсветку сделал через условное форматирование. Если вы делаете обычную вставку, то условное форматирование с ячейки слетает и подсветки не будет. Чтобы этого избежать, используйте Специальную вставку -> Значения.  

Попробовал сделать, чтобы при вставке значения не из списка подсвечивалась ячейка.
Но! Вставка должна быть только вставкой значения (иначе условное форматирование слетает).
Формула для условного форматирования: =ЕСЛИ(И(E2<>"";СЧЁТЕСЛИ('Период Активности'!A:A;E2)=0);1;0). форматирование установлено на ячейки E2:E100.
p.s. в моем примере ваш макрос удален.  

Для Вашей задачи подойдет стандартный функционал сводных таблиц.
Прикрепляю пример.
Форматирование сводной таблицы можно сделать собственное (заливку, границы и т.д.)

Вроде понял идею. Посмотрите.

Если это делать с помощью стандартных формул - можно добавить столбец с критерием (например, значения 1 и 2), и при заполнении столбца с номенклатурами проставлять это критерий, по которому можно будет понять, где файл находиться. Но это может быть удобно, только в том случае, если количество номенклатур разумное. Если больше, то тут на помощь приходит макрос, тогда никаких "ограничений", даже по количеству мест поиска.
 

 м

Можно, нужен конкретный пример.
Если есть критерий сортировки рецептур по разным папкам и этот критерий в самом названии, то это совсем просто.

Попробуйте формулу =ЕСЛИ(B2="";"";ГИПЕРССЫЛКА("C:\Users\Documents\"&D2&".xlsx";D2))  

Не понимаю, что еще нужно плюсовать? можете пояснить.

Если нужно просто сравнить две таблицы, то условным форматированием можно так

Исправил. На почту отправил.
Ошибка была все та же - в диспетчере имен в формуле выдает #ССЫЛКА.
Исправил по аналогии с другим "рабочим" макросом FillMassiv и рабочим диапазоном в диспетчере «MassivRange».
Проверяйте!

Советую все-таки поработать над файлом и выложить на форум.
Стоит удалить лишние данные и оставить пару записей для примера.
Если не получится, отправляю почту в личку.  

Вы поменяли файл? Или вопрос уже снят?

В "диспетчере имен" три имени с ошибочной формулой. VBA не может найти нужный диапазон.