#Excel: Using Conditional Formatting to Highlight 3D Formulas with Defined Names by David Hager

There was a comment on LinkedIn about my post about using CF to highlight 3D formulas

“Since I never use a direct reference (or, come to that, enter a formula without naming the range to which it applies) any 3D reference I might use would pass under the radar. Unless, of course, you have an array UDF which will parse the formula to yield a set of references; in which case can I put in an order?”

Initially, I replied that it was not possible. But, the challenge was irresistable. I started working on the problem and, after a number of dead-ends, I was able to come up with a solution. It required a VBA function to return an array of defined names.

Function DefinedNameArray() As Variant

Application.Volatile

Dim Arr As Variant

nCount = ActiveWorkbook.Names.Count

ReDim Arr(1 To nCount)

For N = 1 To nCount

cPos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “:”)

ePos = InStr(1, ActiveWorkbook.Names(N).RefersTo, “!”)

If cPos < ePos Then

Arr(N) = ActiveWorkbook.Names(N).Name

Else

Arr(N) = “”

End If

Next

DefinedNameArray = Arr

End Function

What the VBA function does is return an array of defined names, but only places the items meeting the correct criteria for a 3D formula in the final array (which is the same concept using in the initial article).

https://dhexcel1.wordpress.com/2017/04/24/excel-using-conditional-formatting-to-highlight-cells-containing-native-3d-formulas-by-david-hager/

In this case, the InStr function was used to locate the positions of the first colon and exclamation point in the RefersTo string and the values are compared. If cPos<ePos, then the name is added to the array and a null string added otherwise. This array is used in the following formula to find if a 3D defined name is part of the string returned by the FORMULATEXT function. It was defined for use as a CF formatting formula, as shown below (F5 was the active cell when defined).

Is3DDefinedName=MATCH(TRUE,IFERROR(FIND(IFERROR(DefinedNameArray(),””),FORMULATEXT(F5))>1,FALSE),0) Both F5 and F7 contain formulas using 3D defined ranges.

Peter, thanks for the challenge!

You can download the example file here.

CFDefinedNames