# Counting Colors

#### nathanpeattie

##### New Member
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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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

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)?

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

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?

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.

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!

I figured It Out!

Thanks for the help!
This is a great board!

Replies
1
Views
198
Replies
15
Views
606
Replies
5
Views
131
Replies
9
Views
251
Replies
8
Views
868

1,217,507
Messages
6,137,033
Members
450,041
Latest member
MM2024

### 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.

### Which adblocker are you using?

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

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