Conditional Formatting for Spilled Arrays

Maybe this will help you a bit.

Code in the workbook module.

I included...

• CreateExpandedNamedRange — creates or updates a workbook-level named range that refers to an explicit start:end inside a container range (safe fallback to a single-cell if requested size doesn't fit).
• UpdateCF_AppliesTo_FromNamedReferences — scans conditional formatting rules and, where it finds a formula that contains a named range, updates that rule's AppliesTo to the named range's current RefersToRange.
• CreateExpandedNamedRangeAndUpdateCF — wrapper to create a named range and immediately update CF rules (convenience).
• Detailed comments, usage examples, and safety notes inside the module.

My answers are voluntary and without guarantee!