IF? VBA? I don't know? IF statement based on formatting (Underline, Bold, Cell Colour, etc.)

[JakeSherlock]

So. We have a file and in that file is a list. and in that list are numbers that are conditionally formatted based on what the number is. And some of those numbers are underlined as the numbers that need to be pulled for counting. I need to total up how many are underlined based on what colour they are.

Example;

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]256[/TD]
[TD="align: center"]246[/TD]
[TD="align: center"]267[/TD]
[/TR]
[TR]
[TD="align: center"]359[/TD]
[TD="align: center"]302[/TD]
[TD="align: center"]354[/TD]
[/TR]
[TR]
[TD="align: center"]225[/TD]
[TD="align: center"]222[/TD]
[TD="align: center"]233[/TD]
[/TR]
[TR]
[TD="align: center"]65[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]41[/TD]
[/TR]
[TR]
[TD="align: center"]358[/TD]
[TD="align: center"]342[/TD]
[TD="align: center"]328[/TD]
[/TR]
</tbody>[/TABLE]

2 total
2 total
1 total

Now I thought that maybe I could use IF, but I am unaware of what IF statement may be used for formatting based IFs.? If IF is the right IF? If we are going to use VBS, I'm a noob and may not understand lingo.

Thanks for help in advance.

-Jake

 

[bbott]

Formulas cannot detect format, so in that sense you would need to use VBA. However....you mention that these cells are conditionally formatted. Are the underlines a result of conditional formatting or are those done manually?

 

[JakeSherlock]

Underline is manual. Colour is auto (Conditional).

-J

 

[bbott]

Underline is manual. Colour is auto (Conditional).

-J

OK, then you'll need to use VBA. This assumes your data is in A1:C5. You can change the highlighted range to your actual range.

Sub CountUnderline()

    Dim s As Range
    Dim Aqua As Long
    Dim Blue As Long
    Dim Pink As Long
    
    For Each s In Range("[B][COLOR=#ff0000]A1:C5[/COLOR][/B]").Cells
        If s.Font.ColorIndex = 8 And s.Font.Underline = xlUnderlineStyleSingle Then
            Aqua = Aqua + 1
        ElseIf s.Font.ColorIndex = 5 And s.Font.Underline = xlUnderlineStyleSingle Then
            Blue = Blue + 1
        ElseIf s.Font.ColorIndex = 38 And s.Font.Underline = xlUnderlineStyleSingle Then
            Pink = Pink + 1
        End If
    Next s
    
    MsgBox ("Aqua count = " & Aqua & Chr(10) & "Blue count = " & Blue _
        & Chr(10) & "Pink count = " & Pink)
        
End Sub