Check for/Detect Dynamic Array Spill in Excel

Question

I want to check for/detect cells that contain values spilled from a dynamic array.

Let's say I have data in the cells A1:A10 and I write the formula =A1:A10 in the cell B1, then B1 itself will contain the actual formula and the values in B2:B10 will be spilled from the dynamic array.

I want to be able to distinguish between the actual formula and the spilled values of the array. More explicitly: I want to write a conditional formatting rule that highlights spilled values (and only spilled values) of dynamic arrays on the sheet.

I would also be happy with a VBA public function that I can use in the conditional formatting, in the sense of something like =IsSpilledValue(B:B) returning TRUE or FALSE.

This question is not a duplicate of "Conditional formatting on a spilled range" in my understanding.

Edit 1:

The reason that I want to highlight only the spilled cells is so that users that are somewhat unfamiliar with dynamic arrays do not try to make changes in the spilled cells but only in the cell that contains the actual formula. However, I have to allow for complete manual override of other users, so locking cells is not an option. Highlighting would really work the best here.

Answer 1

This is the function you are looking for:

Public Function isSpilledValue(c As Range) As Boolean
isSpilledValue = c.HasSpill 
End Function

Public Function isSpilledValueAndNotSpillParent(c as range) as boolean
isSpilledValueAndNotSpillParent= c.HasSpill And c.Address <> c.SpillParent.Address
End Function

SpillParent returns the cell containing the formula.