Weird UDF problem

[bobsan42]

Hello guys
I wasn't expecting to post a question here, but here it is:
related to another post here (https://www.mrexcel.com/forum/excel...ng-cell-value-based-another-cells-colour.html) I decided to make a small UDF to determine the color of a cell.
At first it all went well:

Function CellColor1(rng1 As Range)
    CellColor1 = rng1.Interior.Color
End Function

No problem here - I enter the formula in a cell =CellColor1(E15), select the cell to inspect and I get the result as expexted.
However - next thing I decided was to get the actual color set by conditional format. So I change to this:

Function CellColor1(rng1 As Range)
    CellColor1 = rng1.DisplayFormat.Interior.Color
End Function

I refresh the formulas in the sheet and I end up with #VALUE ! error instead.
So I check it in the Immediate window:

?CellColor1(activecell)

And there I got the expected result: 16777215
But the cells with the function still show a #VALUE ! error ???!!!
Any ideas?

BTW - currently working on Win10 + Office 365 ProPlus

 

[mikerickson]

I suspect that .DisplayFormat (like .Find or .SpecialCells) will error when in a UDF used on a worksheet, but work fine when the UDF is called by a VB routine.

 

[bobsan42]

REALLY ?!
Any ideas for a workaround?

PS: I got an idea, looked it up and found a ready solution here: https://stackoverflow.com/questions...ditionally-formatted-cell-in-excel-2/33812465
Not my favorite approach but in summary: looping through all conditions, evaluating them and getting the format set for them, hoping that eventually we get it right.

Any other ideas appreciated.

 

[mikerickson]

Not really. One problem with color based formulas, is that changing a cell's color (either manually or by CF) does not force calculation.

 

[bobsan42]

The original source of the solution in stackoverflow should be here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=190

Working at first glance.
However given the increase of complexity of conditional formatting I think it will not be 100% bulletproof - testing and evaluation pending

 

[bobsan42]

Not really. One problem with color based formulas, is that changing a cell's color (either manually or by CF) does not force calculation.

Good point Mik, thanks for the tip.