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

Links? Where??
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
 
Upvote 0
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)?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top