Extract font color from cells with multiple colors in a cell

Question

I have an Excel worksheet that I am trying to retrieve in a MySQL database.

• I use VBA to write data as text to a file, and then upload it to the database.
• There are lines in the worksheet cells that have been color coded.
• Colors have a specific meaning, so I want to save them when I move the values ​​to the database (I have a special column in the database where I list the colors).

The fact is that in some cells there are lines separated by commas, and on one side of the comma the line is black, on the other hand it is blue (or vice versa, and there can be more commas and lines in the cell).

what i tried

I can extract the fine lines using the Split function in VBA, but which loses the formatting of the line.

I can get the color of the cell using Range("mycell").Font.ColorIndex , but returns NULL if there is more than one color in the row.

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

Example: one cell may contain the following row

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

Answer 1

I would use .Font.Color to drop the RGB values, but you can change it to ColorIndex if you want.

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 

Answer 2

You can use the following and then create a dictionary / collection / array for storing colors and save only unique values ​​or any other solution that suits your situation. It 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 3

After the response from Excel Hero, I managed to compile this code that fulfilled my needs, which are a function that returns colors in the collection (there may also be an array):

 Function GetColors(i, j) As Collection Dim l&, k&, r As Range Dim c As Collection Set c = New Collection Set r = ActiveSheet.Cells(i, j) Do l = Len(r) If Not InStr(r, ",") Then k = InStr(k + 1, r, ",") ElseIf Not InStr(r, " / ") Then k = InStr(k + 1, r, " / ") End If If k Then l = k - 1 c.Add r.Characters(l, 1).Font.ColorIndex Loop Until k = 0 Set GetColors = c End Function