Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Excel Cells By Cell Color, By Text Color, and by Specific Text

    I am using Excel 2010.

    I would like some help in creating a formula that does three things:

    Counts the cells in a range by cell color & also by the color of the text & also by matching text.

    So if I have a cell shaded yellow, the text is red, and the text is USA I want to count that as a single occurrence.

    With the formula I feel I can edit to look for different colors, different text colors, and different text.

    Thanks.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Welcome to the Lounge!

    You can use a custom function to do this.
    see attached example file.
    Code:
    Function zCountif(rng, cell)
    
    Application.Volatile
    
    Set zRange = rng
    Set zCell = cell
    
    zValue = cell.Value
    zFontColor = zCell.Font.Color
    zCellColor = zCell.Interior.Color
    
    zCountif = 0
    
    On Error Resume Next
    For Each cell In zRange
    If cell.Value = zValue Then
    If cell.Font.Color = zFontColor Then
    If cell.Interior.Color = zCellColor Then
    zCountif = zCountif + 1
    End If
    End If
    End If
    Next
    
    On Error GoTo 0
    
    End Function
    (This function could be improved)

    zeddy
    Attached Files Attached Files

  3. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Homework?
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  4. #4
    New Lounger
    Join Date
    Apr 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    LOL, not homework. This is for a project at work. The project is data migration from one billing system to another. I have huge spreadsheet with various color coded cells & text. I'm trying to sort the data into various groups so that I can schedule specific migration events for each group.

  5. #5
    New Lounger
    Join Date
    Apr 2015
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Zeddy.... I will try this and let you know if I have additional questions.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    ..you should be aware that changing a cell's font color or cell color does NOT trigger a change or calc event, so you should be careful! Changing the text contents of a cell will trigger a calc event.

    zeddy

Tags for this Thread

Posting Permissions

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