Find text in a cell that is a different font color

[jscranton]

Someone went through a workbook and changed the font within cells (NOT the whole cell) to a different color. I need to capture all the text with a different font color (yellow). I can use this if i step through every character in every cell:

If cel.Characters(y, 1).Font.ColorIndex = 6 then

is there a way to tell if a cell contains a certain font color when it might contain more than one color (without looping through the entire text string)?

 

[Michael M]

Short answer is no.

VBA will have to search each cell by character to check for color changes !

 

[Rick Rothstein]

Short answer is no.

VBA will have to search each cell by character to check for color changes !

No, that is not correct... there is a way to test the entire cell.

If Range("A1").Font.Color = Null Then
  MsgBox "The cell has two or more characters with different font colors"
End If

The OP will need to examine each cell in a loop and if the above test for that cell is Null, then fix the color (I guess). I think the OP will also have to test to see if the font color is not black as well (just in case all of the text were made a different color.

 

[Michael M]

Thanks Rick, but does this assume the default color constant is Black ??

Range("A1").Font.Color = Null

 

[Michael M]

On testing that code didn't return the message regardless of font color OR times it appears !

Range("A1").Font.Color = Null

 

[Rick Rothstein]

Thanks Rick, but does this assume the default color constant is Black ??

Range("A1").Font.Color = Null

I'm sorry, I was distracted before I had to do a chore for my wife. I should have remembered that you cannot test for Null with the equal operator... VB has a function (IsNull) that should be used. Try this code instead of what I posted earlier...

If IsNull(Range("A1").Font.Color) Then
  MsgBox "The cell has two or more characters with different font colors"
End If

 

[Michael M]

Yep, that works...thank you for the updated infor.....that should make to OP's task a little quicker as well..