Counting cell colours that are coloured via CF

[tyija1995]

Hi all,

I have a range of values (B2:B11) that have multiple Conditional Formatting (CF) rules, I am now trying to make a UDF that will count how many cells are orange for example.

I have used the following procedure to decipher what the colour index is of the orange cell:

Sub ColourMessage()
    MsgBox ActiveCell.DisplayFormat.Interior.ColorIndex
End Sub

Which returns 44 when I test, this is fine, so I have then made the following but it doesn't quite work:

Function COUNTORANGE(rng As Range)
    Dim cell As Range
        COUNTORANGE = 0
        For Each cell In rng
            If cell.Interior.ColorIndex = 44 Then
                COUNTORANGE = COUNTORANGE + 1
            End If
        Next cell
End Function

This returns 0 even though all of the cells are orange for testing purposes.

If I change

If cell.Interior.ColorIndex = 44 Then

to:

If cell.DisplayFormat.Interior.ColorIndex = 44 Then

I get #VALUE ! when I use the function.

With B2:B11 CF'd to orange, I am expecting to see =COUNTORANGE(B2:B11) return 10. Any ideas?

Thanks!

 

[Joe4]

You forgot "DisplayFormat" in your UDF, i.e.

Function COUNTORANGE(rng As Range)
    Dim cell As Range
        COUNTORANGE = 0
        For Each cell In rng
            If cell.[COLOR=#ff0000]DisplayFormat.[/COLOR]Interior.ColorIndex = 44 Then
                COUNTORANGE = COUNTORANGE + 1
            End If
        Next cell
End Function

you had it in your test Procedure, but left it out in the UDF.

 

[tyija1995]

You forgot "DisplayFormat" in your UDF, i.e.

Function COUNTORANGE(rng As Range)
    Dim cell As Range
        COUNTORANGE = 0
        For Each cell In rng
            If cell.[COLOR=#ff0000]DisplayFormat.[/COLOR]Interior.ColorIndex = 44 Then
                COUNTORANGE = COUNTORANGE + 1
            End If
        Next cell
End Function

you had it in your test Procedure, but left it out in the UDF.

Hi Joe,

Thanks for the response, but in my original post I stated that I had also tried with that line in the UDF, unfortunately it only returns an error. (#VALUE!)

 

[Fluff]

Unfortunately you cannot use DisplayFormat in a UDF.
To count cells that are coloured by CF you either need to use the same rule as the CF, or have a look at Chip's site http://www.cpearson.com/excel/CFColors.htm

 

[Joe4]

It seems to work for me when used within VBA, but not directly on the worksheet (not sure why).

So this worked for me:

Sub tester()
    Dim rng As Range
    Set rng = Range("B2:B11")
    MsgBox COUNTORANGE(rng)
End Sub

Not sure why it these UDF falls over itself when called directly on the worksheet...

EDIT: Looks like Fluff knows the answer!

 

[Joe4]

BTW, sorry about overlooking the end of your orignal. I skimmed your original question, and copied your code, and was messing around with it and testing it all in VBA.

 

[tyija1995]

Unfortunately you cannot use DisplayFormat in a UDF.
To count cells that are coloured by CF you either need to use the same rule as the CF, or have a look at Chip's site http://www.cpearson.com/excel/CFColors.htm

Thanks for this information, I will take a look at that website!

 

[tyija1995]

BTW, sorry about overlooking the end of your orignal. I skimmed your original question, and copied your code, and was messing around with it and testing it all in VBA.

Not to worry Joe, I skim read and miss things all the time too ! The procedure you posted does indeed work but for a function, sadly it doesn't, as Fluff has mentioned also.