• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

UDF to count conditionally formatted cells (if condition is met)

C

cacos

Member
Hi there everyone

I'm going crazy with this one. Can't seem to make a UDF to work to count how many cells are colored "green" or X by conditional formatting.

I've looked everywhere and only found UDFs that tell you whether there is a condition or not, or what the possible colors are if any of those are met. But none tell you the actual color of that conditionally formatted cell.

The conditional formatting applied are top10%, bottom 10% rules. The need is to count, on a given row, how many cells meet each respective condition.

I'm uploading a sample file, hoping that someone has encountered this before. I'm using Excel 2010.

Thank you!
 

Attachments

  • sample.xlsx
    9.6 KB · Views: 5
Try using "Range.DisplayFormat.Interior.Color" Or "Range.DisplayFormat.Interior.ColorIndex" to read conditionally formatted cell's color.

For conditionally formatted cells, you can't use "Range.Inteiror.Color" or "Range.Interior.ColorIndex"

Edit: Note that "Range.DisplayFormat" was introduced in 2010 and can't be used in older versions. For older versions, you'll need to paste range to something like Paint and use it to read RGB color or other color index (especially when color scale CF is used).
 
Ah, didn't know that ".DisplayFormat" doesn't work when used in cell.

Can't you just use VBA to return value to cell?
 
Just thought of work around.

Once CF is applied, then use VBA to read color using ".DisplayFormat". Replace all CF with simple ".Interior.Color". Then use UDF based on ".Interior.Color".

I know it's a kludge but can't think of another way in UDF.
 
You must log in or register to reply here.
Back
Top