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.
=AND(A1>LOWEST VALUE,A1<HIGHEST VALUE
and format cells you can select which color for text to display.