0

So I am preparing a report of sorts in Excel and was wondering if I can color text in a certain column according to what values are in it using conditional formatting? To be precise there will be two groups of text, and color group 1 as green and the other another color.

Here is an example image - where I would like if A, B, and C occur in the cell, it be colored red, and if X, Y or Z occurs in the cell, it is colored Green. This is while ignoring the numbers inside the braces and the braces themselves.

Example image -

enter image description here

Is this even possible using conditional formatting? Any help is appreciated.

1
  • 2
    You can't do this via conditional formatting, since that can basically apply the same format to the entire cell. You'd need VBA, and walk through the cell contents and color characters as needed.
    – BruceWayne
    Oct 22 '18 at 14:04
1

Since VBA codes are appropriate for the task therefore, I would like to suggest MACRO to fix the issue.

enter image description here

Sub ColourPartiaText()

Dim Row As Integer, Col As Integer
Dim CurrentCellText As String
Col = 1


For Row = 2 To 5


    CurrentCellText = ActiveSheet.Cells(Row, Col).Value


    HotStartPosition = InStr(1, CurrentCellText, "A")
    CoolStartPosition = InStr(1, CurrentCellText, "B")
    CoolStartPosition1 = InStr(1, CurrentCellText, "C")
    CoolStartPosition2 = InStr(1, CurrentCellText, "X")
    CoolStartPosition3 = InStr(1, CurrentCellText, "Y")
    CoolStartPosition4 = InStr(1, CurrentCellText, "Z")


    If HotStartPosition > 0 Then
        ActiveSheet.Cells(Row, Col).Characters(HotStartPosition, 1).Font.Color = RGB(255, 0, 0)
    End If


    If CoolStartPosition > 0 Then
        ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition, 1).Font.Color = RGB(255, 0, 0)
    End If

If CoolStartPosition1 > 0 Then
        ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition1, 1).Font.Color = RGB(255, 0, 0)
    End If

    If CoolStartPosition2 > 0 Then
        ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition2, 1).Font.Color = RGB(51, 153, 51)
    End If

    If CoolStartPosition3 > 0 Then
       ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition3, 1).Font.Color = RGB(51, 153, 51)
    End If

    If CoolStartPosition4 > 0 Then
       ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition4, 1).Font.Color = RGB(51, 153, 51)
    End If

Next Row
End Sub

How it works:

  1. Write data in Column A from Row 2 to 5.
  2. Press Alt+F11 to open VB Editor window.
  3. Copy & Paste this code as standard module.
  4. Finally RUN the Macro.

N.B

  • In code Col=1 and For Row = 2 To 5 are editable, you can adjust Column & Row position according to data placement in your Sheet.
  • Color codes are also editable.
3
  • This worked wonderfully! I had to make a few changes for my real implementation but this is more or less what I need. Thank you! for others - Characters(HotStartPosition, 1) change the "1" to whatever number of characters in your actual text to be colored.
    – SidSen
    Oct 24 '18 at 3:15
  • Glad to help you ,,, keep asking ☺ Oct 24 '18 at 4:06
  • Yes,, variable name,,, Character position are editable you can adjust them as you need !! Oct 24 '18 at 4:08

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