UDF Error with automatic calculations

Onecubed

New Member
Joined
Jul 1, 2019
Messages
2
The formula below is being used in a table to see which cells in the range have a particular conditional formatting color. When I enter the formula in the first row and excel auto-fills the other rows, the formula works. If I turn on automatic calculations, it gives #Value !. If manual formulas is on and I press Calculate Sheet, it returns #Value !. I am new to VBA and haven't been able to find an answer in my research yet. My best guess is the order of calculations when excel calculates the whole page verse when just the column is calculated.

Code:
Function ConditionalColor(Rng, Clr)
  
    Dim c
    ConditionalColor = False
    For Each c In Rng
        If c.DisplayFormat.Interior.ColorIndex = Clr Then
            ConditionalColor = True
            Exit For
        End If
    Next
    
End Function
 
Last edited by a moderator:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can't use DisplayFormat in a UDF.
 

Onecubed

New Member
Joined
Jul 1, 2019
Messages
2
Thank you for the response. I was able to correct it by removing this method from the formula all together.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,462
Messages
5,636,419
Members
416,917
Latest member
koto1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top