one way:

Public Sub ColorCells()
Dim result1 As Variant
Dim result2 As Variant
Dim rFormat As Range
Do
result1 = Application.InputBox( _
Prompt:="Color red above:", _
Title:="ColorCells()", _
Default:=10000, _
Type:=1)
If result1 = False Then Exit Sub 'user clicked Cancel
Loop Until result1 < ""
Do
result2 = Application.InputBox( _
Prompt:="Color green below:", _
Title:="ColorCells()", _
Default:=1000, _
Type:=1)
If result2 = False Then Exit Sub 'user clicked Cancel
Loop Until result2 < ""
With Selection
On Error Resume Next
Set rFormat = Union(.SpecialCells( _
xlCellTypeConstants, xlNumbers), _
.SpecialCells(xlCellTypeFormulas, xlNumbers))
On Error GoTo 0
If Not rFormat Is Nothing Then
With rFormat
.FormatConditions.Delete
With .FormatConditions.Add( _
Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:=result1)
.Font.ColorIndex = 3
End With
With .FormatConditions.Add( _
Type:=xlCellValue, _
Operator:=xlLess, _
Formula1:=result2)
.Font.ColorIndex = 10
End With
End With
End If
End With
End Sub



In article ,
ian123 wrote:

Thanks very much, working exactly as i hoped. There is one thing left
to 'tweak' if i may be so bold... on running the macro if there are any
text cells in the selection the color of these entries are changed to.

Is it possible to avoid this? If not, don't worry about it - its a
small iritation to live with!!!