Format Text in Cells

Question

Excel makes it easy to format the background colors of a range of cells to a range / spectrum of colors.  I select “Conditional Formatting” → ”New Rule” → ”Format all cells based on their values” and Format Style: “2-Color Scale”. This changes the background (fill) color of the cells, based on their values.  For example, this:

    

will set the fill color of cells containing the lowest value in the range to red, the highest value to blue, and intermediate values to shades of fuchsia, pink, violet and purple:

How can I change the text color using the same conditions?

Answer 1

I would like to suggest you simple VBA Code to Conditional Format the TEXT data in the specified Range.

It's most shortest method, now what you have to do, in the Data Range pick the TEXT has maximum occurrence and you may Sort them if possible.

Private Sub CommandButton1_Click()

lRow = Range("A" & Rows.Count).End(xlUp).Row
Set Mrng = Range("A120:A" & lRow)

For Each Cell In Mrng

    Select Case Cell.Value
        Case "Yes"
            cell_colour = 9
        Case "No"
            cell_colour = 5
        Case Else
            cell_colour = 3
        End Select

    Cell.Font.ColorIndex = cell_colour
Next

End Sub

NB: Apply the Colour Index Code & also alter the Data Range, as you need.

Hope this help you.

Note, I've tested this code before I post it here. If anybody differs from it, write the REASON before you DOWN VOTE.

Answer 2

There doesn’t seem to be a native way of doing this from the GUI.  However, it’s not hard in VBA.  This routine will set the foreground / text color of every cell in the Target range to a color linearly interpolated between the Lo color and the Hi color, based on how the value in the cell compares to the Lo_Val and the Hi_Val:

Sub Colorize(Target As Range, Lo_Val, Hi_Val, Lo_R, Lo_G, Lo_B, Hi_R, Hi_G, Hi_B)
    Dim R, G, B As Long

    delta_val = Hi_Val - Lo_Val
    If (delta_val <= 0) Then
        MsgBox "Lo_Val and Hi_Val must not be the same, or in the wrong order."
        Exit Sub
    End If
    If (Application.Min(Lo_R, Lo_G, Lo_B, Hi_R, Hi_G, Hi_B) < 0 _
                Or Application.Max(Lo_R, Lo_G, Lo_B, Hi_R, Hi_G, Hi_B) > 255) Then
        MsgBox "Invalid color(s)."
        Exit Sub
    End If
    Delta_R = Hi_R - Lo_R
    Delta_G = Hi_G - Lo_G
    Delta_B = Hi_B - Lo_B
    For Each c In Target
        If (c < Lo_Val Or c > Hi_Val) Then
            c.Font.Color = 0        ' Black
            ' Alternatively, set values < Lo_Val to the Lo color
            ' and set values > Hi_Val to the Hi color.
            ' Or do nothing, leaving them with whatever color they have now.
        Else
            Diff_Val = c - Lo_Val
            ' We probably could just compute (c - Lo_Val) / delta_val
            ' at this point, but I'm not sure how well that would work
            ' with the rounding.
            R = Round(Lo_R + Delta_R * Diff_Val / delta_val)
            G = Round(Lo_G + Delta_G * Diff_Val / delta_val)
            B = Round(Lo_B + Delta_B * Diff_Val / delta_val)
            ' Use 256& to get a constant with value 256 and type Long.
            c.Font.Color = R + 256& * G + 256& * 256& * B
        End If
    Next c
End Sub

See How do I add VBA in MS Office? for general information on how to enter and use this.

I’ve written this as a routine with parameters for flexibility.  AFAIK, it’s impossible to call a VBA routine with parameters from the normal Excel UI.  You may want to define a stubby macro (with no parameters) that calls the above with the parameters you want; e.g.,

Sub Macro1()
'
' Macro1 Macro
'
    Call Colorize(Range("A1:J1"), 1, 10, 255, 0, 0, 0, 0, 255)
End Sub

which will set low numbers (1 and above) to shades of red, and high numbers (10 and below) to shades of blue.  For example, using the numbers from the question, this gives us

(where I manually increased the font size to make the colors more visible).

You can then call this from “Developer” → “Code” → “Macros”, or bind it to a button.  Or, if you want the formatting to happen automatically whenever you change a value, you can use a Worksheet_Change routine to call Colorize.  And, to get this to behave more like the conditional formatting, you could code it to set Lo_Val and Hi_Val to the Min and Max values currently in the range.

Notes:

• The Dim R, G, B As Long line seems not to be needed.  But I get overflow errors when I use plain 256 instead of 256&.
• The science of how the human eye and the human brain perceive color is very complicated.  Linear interpolation of RGB values is probably not the best algorithm to generate a graded range / spectrum of colors.

Answer 3

Yes it's very much possible, to do this follow these steps.

1. Select the Data Range.
2. Click Conditional Formatting.
3. Hit New Rules.
4. Find & Select the option "Format Only Cells That Contains"
5. In Edit the Rule Description.
6. Set "Format Only Cell With", Cell Value & Equals to then put the Text, for example East, then hit FORMAT Button and Select the appropriate Text Colour.

Note, You need to repeat Step 1 to 6 for other Text & Colour for Same Data Range.

If Possible Sort the Data Range so Text will in Groups then select & apply Condition Formatting.

Hope this help you.