# 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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### nathanpeattie

##### New Member
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
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
yes that what I want h1 for green and I1 for red

#### nathanpeattie

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

#### nathanpeattie

##### New Member
I figured It Out!

Thanks for the help!
This is a great board!

Replies
3
Views
501
Replies
15
Views
368
Replies
18
Views
685
Replies
8
Views
296
Replies
3
Views
231

1,171,064
Messages
5,873,579
Members
432,985
Latest member
leahw

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