1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count color cell both (Manual and CF) with VBA

Discussion in 'VBA Macros' started by Jagdev Singh, Aug 11, 2017.

  1. Jagdev Singh

    Jagdev Singh Active Member

    Hi Experts,

    Please let me know if this is possible or not. I have a sheet which contains red cell with both Manual and Conditional Formatting red colored cell. I want to count the number of cells with both options available in the sheet.

    In the attached sample column A is the manually added red color cell. The column B is the conditional formatting driven red colored cells. The output of this is total number of cells available in the sheet i.e. 3.

    Kindly let me know if this is feasible or not.



    Attached Files:

  2. Chihiro

    Chihiro Well-Known Member

    If using manual format, use one of UDFs that's out there.

    Unfortunately, there isn't direct way to count CF color using UDF.

    Alternatives are.
    1. Write enter operation in VBA, then have the code put back the value to desired location. I believe this is only possible for Excel 2010 or later.

    2. Since it's CF, there must be underlying logic that determines color. So, count the values that meet the condition in range using COUNTIF, etc.
  3. p45cal

    p45cal Well-Known Member

    Chihiro is correct; you can write a function to count colours including conditionally formatted colours (using .DisplayFormat) and it will work when called by another sub, it just won't work when you try to call it from a worksheet cell. Most Excel MVPs consider this to be a bug. .DisplayFormat is only available from Excel 2010 onwards.
    In the attached, I've written a sub to count colours whether from CF or manually added, which you can call manually, or by clicking the button on the sheet, or you could assign it a hot-key combination.

    It works like this:
    1. Select the area(s) you want to count the colours in. If you're selecting several non-contiguous areas make sure they don't overlap else you might get colours counted more than once.
    2. Make the last selection the single cell whose colour you want to count. Hold the Ctrl key down while selecting the last cell (and to select non-contiguous areas).
    3. Run the macro.
    The count will appear in the cell immediately to the right of the single coloured cell you selected at the end.
    The code:
    Code (vb):
    Sub ColourCount()
    Set TheRange = Selection
    With TheRange.Areas(TheRange.Areas.Count)
      lCol = .DisplayFormat.Interior.Color
      Set OutPutCell = .Offset(, 1) 'this is what decides which cell the count appears in.
    End With
    For i = 1 To TheRange.Areas.Count - 1
      For Each rCell In TheRange.Areas(i).Cells
        If rCell.DisplayFormat.Interior.Color = lCol Then vResult = 1 + vResult
      Next rCell
    Next i
    OutPutCell.Value = vResult
    'MsgBox vResult
    End Sub

    Attached Files:

    Last edited: Aug 11, 2017
  4. Jagdev Singh

    Jagdev Singh Active Member

    Hi p45cal and Chihiro,

    Thanks for the above information and code. This is really helpful. Appreciated your input on this!


Share This Page