Results 1 to 4 of 4

Count by Cell Text Color

Threaded View

  1. #1
    Registered User
    Join Date
    08-23-2011
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    57

    Count by Cell Text Color

    I would like to count cells in excel based on the color of the text in the cell. I found the code below. It seems to work well, except in regard to one thing: when I have a cell within the range that is formatted to utilize the specific color being identified, the code will count that cell even if the cell has nothing in it. Is there any way to modify this formula such that the count will only include cells within the range that have data in it (or, more specifically if it matters, a number in it)?

    Thanks!!

    Code:
    ------------------------------------------------------------

    Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double
    'Update 20140210
    Application.Volatile
    Dim rng As Range
    For Each rng In pRange1
        If rng.Font.Color = pRange2.Font.Color Then
            CountColour = CountColour + 1
        End If
    Next
    End Function
    -----------------------------------------------------------

    This is the corresponding code I am using for sum, by the way, which seems to work well:

    Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double
    'Update 20140210
    Application.Volatile
    Dim rng As Range
    Dim xTotal As Double
    xTotal = 0
    For Each rng In pRange1
        If rng.Font.Color = pRange2.Font.Color Then
            xTotal = xTotal + rng.Value
        End If
    Next
    SumByColor = xTotal
    End Function
    Last edited by excel-help; 06-23-2016 at 01:13 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Swapping cell content to INCLUDE CELL FORMATTING (Text color, background color, etc)
    By jcpeterson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-10-2014, 10:09 AM
  2. Macro to change cell text color based on darkness of cell fill color
    By cincyshirm61 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-25-2014, 12:11 PM
  3. Replies: 4
    Last Post: 12-29-2013, 11:41 PM
  4. Replies: 0
    Last Post: 09-26-2012, 01:08 PM
  5. Count by Color and Text in a cell
    By BobTerwilliger in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2006, 05:59 PM
  6. count by specific text color in range of cell
    By Tii99 in forum Excel General
    Replies: 2
    Last Post: 04-04-2006, 04:58 AM
  7. change text color based on adjacent cell text color
    By matthewst in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2005, 03:49 PM

Tags for this Thread

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