Count green cells in a row?

[samilynn]

is there a formula or VBA code that can count the green colored cells in a single Row? I'm also going to use it for Red cells btw...if any of this is possible.

Thank you!

Samantha

 

[DanteAmor]

Check:

Counting Cells by Color

Hello MrExcel Nation! I am setting up a planning worksheet to see where I am over/under utilized. The challenge I am having is counting cells by color. There are some activities that require 2 resources and the other cells that have text without any cell color (or white) require just one...

www.mrexcel.com

Count Cells Based on Cell Color

I found the following code online to be able to count how many cells are in a certain color, however its only returning #NAME So I'm using this VBA - Function GetColorCount(CountRange As Range, CountColor As Range, TheValue As Variant) Dim CountColorValue As Long Dim TotalCount As Long...

www.mrexcel.com

 

[Joe4]

It depends on how the cells were colored red/green. If they were colored manually, you will need to use VBA code, like in the links Dante provided.
If they are being colored by Conditional Formatting, you may be able to use the same criteria in a COUNTIFS formula to get your counts.

 

[DanteAmor]

You can use the following CountColor() function, it works for both cells with conditional formatting and cells with manual color.

Dante Amor
	A	B	C	D	E	F
1
2			4			3
3
4	CF	1		Manually
5	Manually	2		Manually
6	CF	1		Manually
7	Manually			Manually
8	Manually			Manually
9	Manually			Manually
10	Manually			Manually
Hoja2

Cell Formulas
Range	Formula
C2,F2	C2	=CountColor(B4:B10,B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B6	Cell Value	=1	text	NO

Put the following in a module. In the cell use CountColor function, like in the example above.

Function CountColor(cRange As Range, cColor As Range) As Long
  Dim c As Range
  Application.Volatile
  For Each c In cRange
    If Evaluate("nIntColor(" & c.Address() & ")") = cColor.Interior.Color Then
      CountColor = CountColor + 1
    End If
  Next
End Function

Private Function nIntColor(ByVal miRango As Range) As Double
  nIntColor = miRango.DisplayFormat.Interior.Color
End Function

 

[samilynn]

You can use the following CountColor() function, it works for both cells with conditional formatting and cells with manual color.

Dante Amor
	A	B	C	D	E	F
1
2			4			3
3
4	CF	1		Manually
5	Manually	2		Manually
6	CF	1		Manually
7	Manually			Manually
8	Manually			Manually
9	Manually			Manually
10	Manually			Manually
Hoja2

Cell Formulas
Range	Formula
C2,F2	C2	=CountColor(B4:B10,B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B6	Cell Value	=1	text	NO

Put the following in a module. In the cell use CountColor function, like in the example above.

Function CountColor(cRange As Range, cColor As Range) As Long
  Dim c As Range
  Application.Volatile
  For Each c In cRange
    If Evaluate("nIntColor(" & c.Address() & ")") = cColor.Interior.Color Then
      CountColor = CountColor + 1
    End If
  Next
End Function

Private Function nIntColor(ByVal miRango As Range) As Double
  nIntColor = miRango.DisplayFormat.Interior.Color
End Function

Thank you Dante, this is PERFECT!!!