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.

 

[Momentman]

I am assuming for example you have chosen to fill the cell with a red color in conditional formatting.

I wrote a user defined function to count the number of cells that have a certain color

for Red, the color is 255, others have their numbers or RGV value

Function CountColors(Rng As Range)
    CountColors = 0
    For I = 1 To Rng.Count
        If Rng(I).Interior.Color = 255 Then '255 is color code for a red fill
            CountColors = CountColors + 1
        End If
    Next I
End Function

If you want to use, use like =Countcolors(A1:A10) and it tells you how many meet the criteria

Just an idea anyways

 

[sthomson]

Thanks.

I have put this code in and applied the following formula =Countcolors(F2:F108) as that is the full column that all my prices are in. i will also be doing the same for columns G - J. I have used conditional formatting to high light the cells. The formula has returned a value of 0 but i know that at the momnet there are 107 that have been highlight red in that particular column. not sure what i am doing wrong.

Thanks.

 

[Momentman]

There are different shades of red, be sure you have the one that shows "red" when you hover a mouse over

 

[Momentman]

Am assuming the other cells either have no fill or are filled with red, i have revised the formula to count those that have a fill, i.e whose colors are not plain, ideally, we should get the same answer. using this as a test just to see if the problem is with the color(maybe not exactly red)

Function CountColors(Rng As Range)
    CountColors = 0
    For I = 1 To Rng.Count
        If Rng(I).Interior.Color <> 16777215 Then
            CountColors = CountColors + 1
        End If
    Next I
End Function

or better still, click on the supposedly red colored cell and run this macro and tell me the output

Sub doloop()
    MsgBox Selection.Interior.Color
End Sub

 

[sthomson]

i have changed the colour so that its just the standard Red (255) but i am still just getting the value as 0 back. Thanks

 

[jim may]

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

 

[sthomson]

Thanks. nothing working. not sure what i am doing wrong.

 

[Snakehips]

fthomson,

If you wish not to use vba then provided that you are ok to use a 'helper' column, e.g. col G, which you can hide if you wish then here is a small scale example.

<tbody></tbody>

Excel 2007
	A	B	C	D	E	F	G
1	Product	Company1	Company2	Company3	Company4	Company5	Min
2	Product1	5	5	4	7	5	4
3	Product2	3	5	4	3	5	3
4	Product3	5	5	4	7	5	4
5	Product4	5	5	4	7	3	3
6	Product5	5	5	4	7	5	4
7	Product6	5	5	4	2	5	2
8	Product7	5	5	4	7	5	4
9	Product8	3	4	4	5	4	3
10	Product9	5	5	4	7	5	4
11	Product10	1	2	3	2	2	1
12	Total	3	0	5	2	1
Sheet2

Cell Formulas
Range	Formula
G2	=MIN(B2:F2)
B12	=SUMPRODUCT(--(B2:B11=$G$2:$G$11))

Adjust the ranges to suit.

Hope that helps.

 

[sthomson]

Thanks. that worked a treat. I had the helper column already in place as part of a requirement anyway so good to be able to use that in other ways.

I don't really understand VBA at the moment but I would love to understand it better.

Thank you to everyone for their help.