Sub ColorCells1()
Dim rgSales As Range
Dim i As Long, j As Long
Dim lngUpperCut As Long
Dim lngLowerCut As Long
Dim res As Variant, res1 As Variant
res = InputBox("Enter Upper cutoff value")
If res = "" Then Exit Sub
If Not IsNumeric(res) Then Exit Sub
res1 = InputBox("Enter LowerValue")
If res1 = "" Then Exit Sub
If Not IsNumeric(res1) Then Exit Sub
lngUpperCut = CLng(res)
lngLowerCut = CLng(res1)
Set rgSales = Selection
rgSales.Font.ColorIndex = xlAutomatic
For i = 1 To rgSales.Rows.Count
For j = 1 To rgSales.Columns.Count
If rgSales.Cells(i, j).Value < lngUpperCut Then
If rgSales.Cells(i, j).Value < lngLowerCut Then
rgSales.Cells(i, j).Font.ColorIndex = 5
Else
rgSales.Cells(i, j).Font.ColorIndex = xlAutomatic
End If
Else
rgSales.Cells(i, j).Font.ColorIndex = 3
End If
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy

"ian123" wrote in message
...
Thanks guys, its working great now!

May i pick your brains on 2 other quick points? Firstly, is it
possible to modify the macro to apply it to a user highlighted
selection rather than the range a1:d6

Secondly, can anyone explain how i can add a second box, similar to the
first but that will find and color a number below a certain amount.
I'm sure i'll be able to copy and modify the old code but how do i get
the macro to bring up the first box and then bring up the second
box...

I appreciate that you have already helped me greatly with your advice
so understand if you have others to help. Once again many thanks for
your advice and consideration of my problem(s)!!!


---
Message posted from http://www.ExcelForum.com/