Check for/Detect Dynamic Array Spill in Excel-CodePudding

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 "

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.

CodePudding user response：

Maybe this will also work for you: =AND(NOT(ISFORMULA(B1)),NOT(ISBLANK(B1)))

However, this will highlight values entered in those cells as well but at least does not require VBA.