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.
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: