Test for text colour in Excel/VBA

[madvinny]

Does anyone know if it is possible to test for the colour of text in a particular cell? i.e. I wish have a spreadsheet where I wish to count the number of cells with text in red, the number of blue and the number of yellow.

 

[logius]

This macro was actually fun to do (I was really bored). :-D

Sub ColorTest()
Dim numRed As Integer
Dim numBlue As Integer
Dim numYellow As Integer
Dim c As Object
Dim TestRange As Range

    numRed = 0
    numBlue = 0
    numYellow = 0
    
    'Set your test range here
    Set TestRange = ActiveSheet.Range("A1:D6")

    For Each c In TestRange
        If c.Characters.Font.ColorIndex = 3 Then
            numRed = numRed + 1
        Else
            If c.Characters.Font.ColorIndex = 5 Then
                numBlue = numBlue + 1
            Else
                If c.Characters.Font.ColorIndex = 6 Then
                    numYellow = numYellow + 1
                End If
            End If
        End If
    Next c
    MsgBox "Red: " & numRed & " Blue: " & numBlue & " _
           Yellow: " & numYellow
End Sub

Let me know if it works for you. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com