+ Reply to Thread
Results 1 to 2 of 2

Count Cells in a Row based on color and date range

  1. #1
    Registered User
    Join Date
    04-14-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    1

    Count Cells in a Row based on color and date range

    I am trying to calculate a range of cells by background color and date range. for instalce lets say i want to count all cells in column A that have a date range from 1/1/09-3/31/09 and that has the blue background fill color. Now i havea function created in vba:

    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult
    lCol = rColor.Interior.ColorIndex
    If SUM = True Then
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
    vResult = WorksheetFunction.SUM(rCell, vResult)
    End If
    Next rCell
    Else
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
    vResult = 1 + vResult
    End If
    Next rCell
    End If
    ColorFunction = vResult
    End Function

    I would normally take this and point it to a cell with the color i am trying to count and then let it go. so the fucntion works correctly. However I am trying to create and addition to this script where it now incorporate the count to specific date ranges i pick and not the whole row. for example I would have certain cells marked as color background blue with dates of 3/1 and 3/2 ect I need to count all these. Is this possible?"

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Count Cells in a Row based on color and date range

    Hello Indnracn2,

    Welcome to the forum. Please take a moment to read the forum rules (link in my signature) and then edit your post to wrap your code in CODE tags. (See here for instructions: http://www.excelforum.com/misc.php?do=bbcode#code)

    Thank you,
    Paul

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1