I have a column of text in which some characters (not cells) are in red font. Is there any way to highlight the cells which contain red characters via conditional formatting? VBA solutions would be fine as well.


I am not aware of any inbuilt functions that check the individual colour of each character within a cell however you can write one in VBA.

This function will iterate through each character and return true if one of the characters is red. Please note: This code only works on the standard red that is available, it will not work on all shades.

'Only works on the standard Red Colour
Function ContainsRed(CellCheck As Range) As Boolean
    For i = 1 To Len(CellCheck.Value) Step 1
        If CellCheck.Characters(i, 1).Font.Color = vbRed Then
            ContainsRed = True
            Exit Function
        End If
    Next i
    ContainsRed = False
End Function

After the formula is created you can create a New Formatting Rule. Click on the home tab, conditional formatting, New Rule.

For use with Column A
Click the last option "Use a formula to determine which cells to format" and use the formula like this: =ContainsRed(A2)
Click Ok and change the applies to Applies To to the column you want, in this instance it will be $A:$A.

Help with the conditional formatting can be found here.

Set ws = Sheets("Cost Analysis compare")

For r = 1 To 104
    For c = 1 To 36
        If (ws.Cells(r, c).Font.Color = 255) Then
            ws.Cells(r, c).Interior.ColorIndex = 44
        End If
    Next c
Next r
  • Please augment the answer to explain why/how this works. – xenoid Aug 14 '17 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.