BobR
05-18-2018, 10:22 AM
I have a spreadsheet with colored cells, Red/Yellow/Green, and found UDFs GetCellColor() and CountCellsByColor() that worked as expected. However, as I wanted to count the instances of two same colored cells, I created myCountCellsByColor() by using CountCellsByColor() as a model.
Unfortunately, when I use =myCountCellsByColor($b$2:$b$11,getcellcolor($e$2),$c$2:$c$11,getcellcolor( $e$2)) it fails with #VALUE!.
Not sure where I went wrong and was unable to find anything in my searches so am hoping someone can identify my mistake.
Thanks in advance.
The UDFs are:
Function GetCellColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()
Application.Volatile
If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If
If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
Next
Next
GetCellColor = arResults
Else
GetCellColor = xlRange.Interior.Color
End If
End Function
****************
Function CountCellsByColor(rData As Range, cellRefcolor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefColor = cellRefcolor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByColor = cntRes
End Function
**************
Function myCountCellsByColor(rData As Range, cellRefColor1 As Range, sData As Range, cellRefColor2 As Range) As Long
Dim indRefColor1, indRefColor2, cntRes As Long
Dim cellCurrent1, cellCurrent2 As Range
Dim i, lastRow As Integer
Application.Volatile
lastRow = rData.Rows.Count
Debug.Print lastRow
cntRes = 0
indRefColor1 = cellRefColor1.Cells(1, 1).Interior.Color
indRefColor2 = cellRefColor2.Cells(1, 1).Interior.Color
For i = 1 To lastRow
If indRefColor1 = cellCurrent1.Interior.Color(i, 1) Then
If indRefColor2 = cellCurrent2.Interior.Color(i, 1) Then
cntRes = cntRes + 1
End If
End If
Next i
myCountCellsByColor = cntRes
End Function
Unfortunately, when I use =myCountCellsByColor($b$2:$b$11,getcellcolor($e$2),$c$2:$c$11,getcellcolor( $e$2)) it fails with #VALUE!.
Not sure where I went wrong and was unable to find anything in my searches so am hoping someone can identify my mistake.
Thanks in advance.
The UDFs are:
Function GetCellColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()
Application.Volatile
If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If
If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
Next
Next
GetCellColor = arResults
Else
GetCellColor = xlRange.Interior.Color
End If
End Function
****************
Function CountCellsByColor(rData As Range, cellRefcolor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefColor = cellRefcolor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByColor = cntRes
End Function
**************
Function myCountCellsByColor(rData As Range, cellRefColor1 As Range, sData As Range, cellRefColor2 As Range) As Long
Dim indRefColor1, indRefColor2, cntRes As Long
Dim cellCurrent1, cellCurrent2 As Range
Dim i, lastRow As Integer
Application.Volatile
lastRow = rData.Rows.Count
Debug.Print lastRow
cntRes = 0
indRefColor1 = cellRefColor1.Cells(1, 1).Interior.Color
indRefColor2 = cellRefColor2.Cells(1, 1).Interior.Color
For i = 1 To lastRow
If indRefColor1 = cellCurrent1.Interior.Color(i, 1) Then
If indRefColor2 = cellCurrent2.Interior.Color(i, 1) Then
cntRes = cntRes + 1
End If
End If
Next i
myCountCellsByColor = cntRes
End Function