Excel VBA User Defined Function that counts cells with conditional formatting

by

You can work around the inability to access DisplayFormat in a UDF using Evaluate

Function DFColor(c As Range)
    DFColor = c.DisplayFormat.Interior.Color
End Function


Function CountCellsByColor1(rData As Range) As Long
    Dim cntRes As Long, clr As Long, cell As Range
    cntRes = 0
    For Each cell In rData.Cells
        'Evaluate the formula string in the context of the
        '  worksheet hosting rData
        clr = rData.Parent.Evaluate("DFColor(" & cell.Address() & ")")
        If clr <> 16777215 Then
            cntRes = cntRes + 1
        End If
    Next cell
    CountCellsByColor1 = cntRes
End Function

More Related Contents:

  1. Save excel file multiple times with file names in another workbook.
  2. Function to convert column number to letter?
  3. Read/Parse text file line by line in VBA
  4. How do I find the last column with data?
  5. Excel VBA: Answer gets “stuck”
  6. 2 Dimensional array from range [closed]
  7. Save multiple sheets to .pdf
  8. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  9. filter out multiple criteria using excel vba
  10. Excel VBA autofilter all but three
  11. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  12. Excel VBA code to copy a specific string to clipboard
  13. Merge values of column B based on common values on column A
  14. How to import a table from web page (with “div class”) to excel?
  15. Excel VBA – Range.Copy transpose paste
  16. How to use FindWindow to find a visible or invisible window with a partial name in VBA
  17. How do you get a Range to return its Name?
  18. Excel VBA: Convert a date string to a Unix timestamp
  19. In Excel, can I use a hyperlink to run vba macro?
  20. How to delete rows in Excel based on criteria using VBA?
  21. Macro to export MS Word tables to Excel sheets
  22. VBA WinHTTP to download file from password proteced https website
  23. Excel VBA – select multiple columns not in sequential order
  24. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  25. Application.WorksheetFunction.Match method
  26. Excel – Using COUNTIF/COUNTIFS across multiple sheets/same column
  27. Excel Hide/Show all tabs on Ribbon except custom tab
  28. Macros Not Showing Up in Macro Table
  29. Excel telling me my blank cells aren’t blank
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment