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