Macro to search blue font text in excel

[cassie123]

Hi all,

I am beginner on macro and I recently got a task from work.

I need a macro code to search cell that formatted in blue font and replace with value from another workbook.

I appreciate all the help and suggestion.

Thanks

 

[robertdseals]

I found this. Not exactly what you are looking for, but might get your started.
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

 

[Jerry Sullivan]

@cassie123,

Are you just wanting to do a Find (search string) and Replace with (a single string from other workbook) process and limit the Find to Cells that have been formatted to have Blue Font? If so, that's pretty easy to do manually by using the Find and Replace dialog (Ctrl-H) > Options > specify the formatting to be searched.

If you are doing something more complex like looking up multiple values from another workbook then please provide more details about what needs to be done.

 

[cassie123]

@cassie123,

Are you just wanting to do a Find (search string) and Replace with (a single string from other workbook) process and limit the Find to Cells that have been formatted to have Blue Font? If so, that's pretty easy to do manually by using the Find and Replace dialog (Ctrl-H) > Options > specify the formatting to be searched.

If you are doing something more complex like looking up multiple values from another workbook then please provide more details about what needs to be done.

However, when I can't seem to replace with the cell that from another workbook.

 

[Jerry Sullivan]

I don't understand what you are trying to do. If you are just replacing one text value from another workbook, I'm suggesting you could just type that value into the Replace with: box.

 

[cassie123]

Thank you. Im still trying out diff ways