Excel user defined function if cell is highlighted then

Question

I have created a user defined function to determine whether cells are highlighted a particular color, and it works in some situations, but not the one I need; Basically it works when that cell is permanently highlighted that color but I am basing it on conditional formatting and the function doesn't work if the cell is that color due to conditional formatting.

    Public Function Active(Rng As Range) As Boolean
        If Rng.Interior.Color = RGB(217, 151, 149) _
            Then Active = True
    End Function

Any help as to why would be much appreciated.

You need to iterate through the cell's conditonal formats - see Bob Phillips code here http://www.xldynamic.com/source/xld.CFConditions.html#specific — brettdj, Dec 17 '12 at 11:48
@brettdj I have tried the code, but seems the item inside rng.FormatConditions is no longer `FormatCondition`, but instead is `ColorScale` . I tried a few different conditional formatting. I am using Excel 2007. — Larry, Dec 17 '12 at 13:25
Take a look at [this SP post](http://stackoverflow.com/questions/7408899/how-do-i-find-the-fill-colour-value-of-a-conditionally-formatted-cell-in-excel-2) — chuff, Dec 17 '12 at 15:18
@Larry the `FromatConditions` are still there as well. `ColorScale` applies to the Color Scale options introduced in xl07 as a conditional formatting option — brettdj, Dec 22 '12 at 14:43

score 0 · Answer 1 · answered Dec 17 '12 at 14:24

0

Can you perhaps use the same criteria in your udf as used by the conditional formatting? Instead of checking for color, check the factors the conditional format checks for.

answered Dec 17 '12 at 14:24

MarioTheHedgehog

306
4
12

score 0 · Accepted Answer · edited May 23 '17 at 11:56

For simple Conditional Formatting (NONE-Color scaling type), you can refer to @brettdj and @Chuff 's external reference http://www.xldynamic.com/source/xld.CFConditions.html#specific

How do I find the fill colour value of a conditionally formatted cell in Excel 2007 using vba?

For color scaling Conditional formatting, I have tried pasteSpecial but it's not working.

The only workable solution for me is to copy the range into MS Word, and then copy the range back into EXCEL. This will remove the conditional formatting. Then you can get the background color as Cells(X,Y).interior.color

Or else you need to implement the color scaling type of conditional formatting yourself.

Excel user defined function if cell is highlighted then

2 Answers2

Linked