Counting Colors

nathanpeattie

New Member
Joined
Dec 17, 2005
Messages
43
I was looking to have A1,B1,C1,D1 shaded green and E1 shaded red.

I would like a code that will give a value in H1 for how many green
& I1 for how many red.

Can this be done.

I don't want to use text the the fields just colors except for the value.

Thanks
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

nathanpeattie

New Member
Joined
Dec 17, 2005
Messages
43
Trying

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function


but will only count 1 color I want to return 2 values
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
Not sure I understand...per your original post, wouldn't you use two formulas (seting the formula in H1 to count Reds and the formula in I1 to count Greens)?
 

nathanpeattie

New Member
Joined
Dec 17, 2005
Messages
43

ADVERTISEMENT

yes that what I want h1 for green and I1 for red
 

nathanpeattie

New Member
Joined
Dec 17, 2005
Messages
43
heres what I got

=COUNTBYCOLOR(A4:A117,3,FALSE) this in I1 for "RED"
=COUNTBYCOLOR(A4:A117,50,FALSE) this in H1 for "GREEN"

so I think I got it however if I change a green to a red the value does not change untill I click in the value field how can I fix this?
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365

ADVERTISEMENT

There really isn't a way to. Changing a background color (or any format for that matter) doesn't cause Excel to update its calculations.

You can experiment with putting something like this in the sheet module:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

which will cause your sheet to recalculate whenever you select a different cell...but, this may or may not be practical for what you're doing.
 

nathanpeattie

New Member
Joined
Dec 17, 2005
Messages
43
now how can I make

=COUNTBYCOLOR(A4:A117,50,FALSE) count both colors and return a value

EG:

=COUNTBYCOLOR(A4:A117,3,50,FALSE) this does not work!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,961
Messages
5,856,531
Members
431,819
Latest member
Tori Murphy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top