Coloured Cell count #Name error

sharpeye

New Member
Joined
Oct 5, 2018
Messages
23
Good morning everyone
I've been trying to put a funcion into my speadsheet to count cells of a certain colour which I know is possible as I already use this on another of my workbooks and it works perfectly but I just cant it to work in another workbook.
Ive copied the exact same vba and inserted it as a new module. I've also looked for other methods to do the same thing and found this code that also gives me the #Name error.
This is the new vba code I have tried

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.ColorIndex = CountColorValue Then
TotalCount = TotalCount + 1
End If
Next rCell
GetColorCount = TotalCount
End Function


This is the code I have used that works on another worksheet

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 GetCellFontColor(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).Font.Color
Next
Next
GetCellFontColor = arResults
Else
GetCellFontColor = xlRange.Font.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 SumCellsByColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes

Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent

SumCellsByColor = sumRes
End Function

Function CountCellsByFontColor(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).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent

CountCellsByFontColor = cntRes
End Function

Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes

Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent

SumCellsByFontColor = sumRes
End Function

But this isn working either and I'm very confused.
Ive used
=GetColorCount(R22:U28, R22)
and
=CountCellsByColor(R22:U28, R22)

and both give #Name as a result

Any ideas??
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for the first Function !!
NB:- See Comment
Code:
Function GetColorCount(CountRange [COLOR="Navy"]As[/COLOR] Range, CountColor [COLOR="Navy"]As[/COLOR] Range) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 [COLOR="Navy"]Dim[/COLOR] CountColorValue [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
 [COLOR="Navy"]Dim[/COLOR] TotalCount [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
 [COLOR="Navy"]Dim[/COLOR] Rcell [COLOR="Navy"]As[/COLOR] Range
 CountColorValue = CountColor.Interior.ColorIndex
 '[COLOR="Green"][B]Set rCell = CountRange ' Incorrect !!!!![/B][/COLOR]
 '[COLOR="Green"][B]NB:- You need to Dim RCell  as a range  variable (as above)[/B][/COLOR]
 '[COLOR="Green"][B]You're trying to look through the range variable CountRange with another variable set to the same range[/B][/COLOR]

 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rcell [COLOR="Navy"]In[/COLOR] CountRange
    [COLOR="Navy"]If[/COLOR] Rcell.Interior.ColorIndex = CountColorValue [COLOR="Navy"]Then[/COLOR]
        TotalCount = TotalCount + 1
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] Rcell
 GetColorCount = TotalCount
 [COLOR="Navy"]End[/COLOR] Function
Regards Mick
 
Last edited:

sharpeye

New Member
Joined
Oct 5, 2018
Messages
23
Many thanks for your reply.
Im still geting #Name ? error using this code as well.
This is really puzzling as I am already using the second lot of VBA code in a different workbook and it works perfectly!!!
 

Forum statistics

Threads
1,078,466
Messages
5,340,484
Members
399,378
Latest member
voodoo1

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top