Extracting font color from cells with multiple colors in the cell

Question

I have an Excel sheet that I am trying to get into a MySQL database.

• I am using VBA to write the data out as text into a file and then upload that to the database.
• In the cells of the worksheet there are strings which have been color coded.
• The colors have a certain meaning so I want to preserve them when I move the values into the database (I have a special column in the database where I enumerate the colors).

The thing is that some cells have strings separated by commas and on one side of the comma the string is black, on the other side it is blue (or vice versa and there can be more commas and strings in the cell).

what I have tried

I can extract the strings fine by using the Split function in VBA but that loses the formatting of the string.

I can get the color of a cell using Range("mycell").Font.ColorIndex but that returns NULL if there is more then one color in the string.

Is it possible to get all the colors of a string?

Example: one cell could contain the following string

"W345, PO3244, 12309"
1. (W345) would be black (colorindex -4105),
2. (PO3244) would be blue (colorindex 47)
3. (12309) would be red (colorindex 3).

Answer 1

You can use the following and then create a dictionary/collection/array to store the colors and only keep unique values or whatever solution fits your situation. This just shows how you can access all the colors.

Sub AllColors()

Dim r As Range
Dim x As Integer

Set r = Selection

For x = 1 To Len(r.Value)
    Debug.Print r.Characters(x, 1).Font.ColorIndex
Next x


End Sub

Answer 2

I'd use .Font.Color to cull the RGB values, but you can change it to ColorIndex if you like.

You can adapt this strategy:

Sub CellColors2CSV()
    Dim j&, k&, c$, r As Range
    Set r = ActiveSheet.Cells(1, 1)
    Do
        j = Len(r)
        k = InStr(k + 1, r, ",")
        If k Then j = k - 1
        c = c & "," & r.Characters(j, 1).Font.Color
    Loop Until k = 0
    c = Mid$(c, 2)
    MsgBox c
End Sub