Pages

Saturday, April 19, 2014

UDF to count cells with format Bold or Italics or Strike-through or Underline

User defined function to count the formatted cells in a range. Snapshot below -


Function count_values(datarng As Range, formatconditon As String)
    'datarng is range to look into
    'formatconditon use B to check bold , i to check italics
    'st to check strikethrough, u to check underline
    Dim ct As Double
    Dim cl As Range
    ct = 0
        For Each cl In datarng
            Select Case UCase(formatconditon)
                Case "B"
                      If cl.Font.Bold = True Then ct = ct + 1
                Case "I"
                      If cl.Font.Italic = True Then ct = ct + 1
                Case "ST"
                      If cl.Font.Strikethrough = True Then ct = ct + 1
                Case "U"
                      If cl.Font.Underline = 2 Or cl.Font.Underline = 4 Or cl.Font.Underline = 5 Then ct = ct + 1
            End Select
        Next
        
    count_values = ct

End Function


How to Use :    =count_values(range _to_check ,"b")

               use b for bold, i for italics, st for strikethrough and u for underline 

No comments:

Post a Comment