search and replace by color font

[ghinkle]

Can anyone help with a search and replace of a color font?

I have been given an Excel 2007 sheet with many thousands of entries where color has been used to indicate a particular chemistry (I work for a biotech company). I have the task of converting the color to an unambiguous string representation prior to loading into an alas color-blind relational database. So for example a red G should be 'g', an orange G should be 'Gf', a yellow G should be 'Gm', etc.

<table style="border-collapse: collapse; width: 96pt;" width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">input</td> <td style="width: 48pt;" width="64">output</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">GGGGG</td> <td>GGgGfG</td> </tr> </tbody></table>
I've tried setting the formatting in Find and Replace, but I only get 'Excel cannot find a match' even though I'm staring at a red G. Making sure the font name and font size are explicitly set did not help.

Any suggestions?

thanks

 

[Bill_Biggs]

I am not as familiar with Excel 2007 as 03, but I don't think you can do a search and replace based on color. If someone knows differently, speak up. I think you will need to write a small macro that will examine each G it comes across, determines its color index, and changes the G appropriately. If you will tell me the colors and what they would convert to, I'll give it a stab, assuming you need the VBA help.

Welcome to the board!

 

[ghinkle]

Any red letter should be converted to lower case.
Any blue letter should have an 'f' appended.
Any orange letter have a 'b' appended.

I'm realizing I will probably have to figure out the color indices.....

thanks,
g

 

[Bill_Biggs]

So Let me make sure I have this:

G=g
G=Gf
G=Gb
G=Gm

Is that it? Are there any other indices? If so, I need them all before I get started, and you need to record them here in the actual color they will be in your chart so I can capture the actual colors you will be using , as there are shades of blue and a few others to consider. There is a chemical engineer that is on this board regular, I will forward this to him as well, just in case he has already done something like this.

 

[ghinkle]

Just these three:
G=g
G=Gf
G=Gf

(yes..two colors mean the same chemistry...keeps me on my toes...)

Unfortunately I don't know how to get the color indices from the Excel sheet....

thanks.

 

[Yard]

Not fully tested, but try

Sub ColourCells()
Dim rngLook, rngC As Range
Dim i As Integer
Dim strNew As String
Set rngLook = Range("F21:F25")
For Each rngC In rngLook
    If Left(rngC, 1) <> "=" And Len(rngC) > 0 Then
        For i = 1 To Len(rngC)
            Debug.Print Mid(rngC, i, 1) & " colour " & rngC.Characters(i, 1).Font.Color
            Select Case Mid(rngC, i, 1) & rngC.Characters(i, 1).Font.Color
                Case "G0"
                    strNew = strNew & "G"
                Case "G255"
                    strNew = strNew & "g"
                Case "G4626167"
                    strNew = strNew & "Gf"
                Case "G65535"
                    strNew = strNew & "Gm"
            End Select
        Next i
        rngC.Value = strNew
        rngC.Font.Color = 0
    End If
Next rngC
 
End Sub

You'll have to do some macro recording to get the right colour codes (I'm using Excel 2007 and got the value 255 from a red letter, 4626167 from an orange letter etc....)

 

[ghinkle]

Thanks for the help.
I had to tweak it a bit (reset the new string), but it worked very well.