Results 1 to 5 of 5

Thread: Coloured Cell count #Name error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Coloured Cell count #Name error

    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 by sharpeye; Aug 18th, 2019 at 07:45 AM.

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Coloured Cell count #Name error

    Try this for the first Function !!
    NB:- See Comment
    Code:
    Function GetColorCount(CountRange As Range, CountColor As Range) As Long
     Dim CountColorValue As Integer
     Dim TotalCount As Integer
     Dim Rcell As Range
     CountColorValue = CountColor.Interior.ColorIndex
     'Set rCell = CountRange ' Incorrect !!!!!
     'NB:- You need to Dim RCell  as a range  variable (as above)
     'You're trying to look through the range variable CountRange with another variable set to the same range
    
     For Each Rcell In CountRange
        If Rcell.Interior.ColorIndex = CountColorValue Then
            TotalCount = TotalCount + 1
        End If
     Next Rcell
     GetColorCount = TotalCount
     End Function
    Regards Mick
    Last edited by MickG; Aug 18th, 2019 at 07:56 AM.

  3. #3
    New Member
    Join Date
    Oct 2018
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Coloured Cell count #Name error

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

  4. #4
    New Member
    Join Date
    Oct 2018
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Coloured Cell count #Name error

    Fixed it =)
    Thank you ever so much, legend

  5. #5
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Coloured Cell count #Name error

    You're welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •