Excel Sum of Colored Special Characters-CodePudding

Hello Stackoverflow community

I was trying to automatically count the Modules Hours per column. Eventually i gave up since i dont have enough experience and the Symbols used are special Characters Colored.

In The Excel Cell it shows "u u"

A Red and Yellow "u u" means 2h
A Green and Yellow "u u" means 1h
A Blue and Yellow "u u" means 0.5h

Is it possible to make a VBA Formula to sum the Characters?

The Code i tried to use so far is:

Public Function SumColorRed(pRange1 As Range, pRange2 As Range) As Double
Application.Volatile
Dim rng As Range
For Each rng In pRange1
  If rng.Font.Color = pRange2.Font.Color Then
        SumColor = SumColor   2
  End If
Next
End Function

Thank you in advance

CodePudding user response：

First thing is simple: The cell text is really u u, just formatted with Font WinDings which will diplay a ◆

If you use rng.Font.Color to read or set the color, this means the color is valid for the whole cell. If you want to get (or set) the color (or other properties like Bold or Italic) for single characters, you can use the Characters-Property. You need to specify the start and the length as parameters, eg rng.Characters(3, 1) to the 3rd character of a cell.

The following code looks to the first and third character of a cell and checks the colors. I am not 100% sure that my color definitions are exact the colors that are used in your sheet, maybe you have to adjust the constant definitions.

Function getColorTime(cell As Range) As Date
    
    Const redCharColor = &HFF&
    Const yellowCharColor = &HC0FF&
    Const greenCharColor = &H50B000
    Const blueCharColor = &HC07000

    Dim c1 As Long, c2 As Long
    c1 = cell.Characters(1, 1).Font.Color
    c2 = cell.Characters(3, 1).Font.Color
    ' Debug.Print c1, c2
    If c1 = redCharColor And c2 = yellowCharColor Then
        getTime = TimeSerial(2, 0, 0)
    ElseIf c1 = yellowCharColor And c2 = greenCharColor Then
        getTime = TimeSerial(1, 0, 0)
    ElseIf c1 = blueCharColor And c2 = greenCharColor Then
        getTime = TimeSerial(0, 30, 0)
    End If
End Function

CodePudding user response：

Here is my two cents:

Formula in C1:

=CountColor(A1:A4)

Refering to UDF:

Function CountColor(rng As Range) As Double

Dim cl As Range

For Each cl In rng
    Select Case cl.Characters(1, 1).Font.Color & "|" & cl.Characters(3, 1).Font.Color
        Case "255|49407"
            CountColor = CountColor   2
        Case "12874308|4697456"
            CountColor = CountColor   1
        Case "49407|4697456"
            CountColor = CountColor   0.5
        Case Else
            CountColor = CountColor
    End Select
Next

End Function

Obviously, you'd want to find out the color-codes for your "aces".