Counting Number of cells that have been highlighted following conditional formatting

[sthomson]

Hi,

I am at my wits end with this. i have searched everywhere but nothing seems to be working for me.

I have a spreadsheet of products that i am looking to purchase for my company.

I have a column for each of the companies that are quoting for these products. I have put in conditional formatting that highlights the cell if its the lowest price in that row.

At the bottom of each column i want to be able to count the number of cells that have been highlighted. this will then show me how many of the 100+ products have the lowest price for each of the companies.

I hope that makes sense and i hope that someone can help me with this.

Thanks for reading my post and i hope to hear from you soon.

 

[Snakehips]

Conditional Formatting uses a separate way or path to work with colors.
Paste the below UDF into a Standard module
In your worksheet enter

=Countcolors(F2:F108)

Function CountColors(myRng) As Long
Dim Rng As Range, C As Range
Dim Cnt As Long
Set Rng = myRng
For Each C In Rng
    If C.FormatConditions(1).Interior.ColorIndex = 3 Then  '3 = Red
     Cnt = Cnt + 1
    End If
Next C
CountColors = Cnt
End Function

Jim, you are correct that for a cell .Interior.ColorIndex will only return it's 'base' format colour and not it's actual colour if cf is applied and true.
Similarly, .FormatConditions(1).Interior.ColorIndex will only return the expected cf colour for that cf condition of that cell.

So your code cannot actually detect nor count the applied cf colours.

Here is a link that explains how cf colour can be detected but it is not as simple as we might imagine.
Conditional Formatting Colors

 

[Momentman]

@snakehips:You are right about that, i was working from the premise of the cells being colored via fill rather than conditional formatting.Silly me

 

[wburbage]

Jim, you are correct that for a cell .Interior.ColorIndex will only return it's 'base' format colour and not it's actual colour if cf is applied and true.
Similarly, .FormatConditions(1).Interior.ColorIndex will only return the expected cf colour for that cf condition of that cell.

So your code cannot actually detect nor count the applied cf colours.

Here is a link that explains how cf colour can be detected but it is not as simple as we might imagine.
Conditional Formatting Colors

Snakehips, is there VBA code that would work for this? I was following this string thinking this is exactly what I'm looking for and then it went over to a helper column which wouldn't work for me due to the complexity of all the formulas. I've been searching and searching and haven't found anything but think VBA would be my best option.

Thanks, Bill

 

[Snakehips]

Bill,

I have had a little play in Excel 2007 and attempted to utilise Chip Pearson's approach ...Conditional Formatting Colors but sadly all to no avail.
Sorry I am unable to help