This ought to get you close...
'--
Sub shadeExceed_R1()
Dim data As Range
Dim datum As Range
Dim standardRow As Long
Dim colNumber As Long
Dim dblCompare As Variant
Dim lngColor As Long

On Error Resume Next
Set data = Application.InputBox(prompt:="Select data range", Type:=8)
If data Is Nothing Then Exit Sub 'User may cancel
On Error GoTo 0

standardRow = Application.InputBox(prompt:= _
"Enter letter of row that contains the standards", Default:="3", Type:=1)
If Val(standardRow) = 0 Then Exit Sub 'User may cancel

colNumber = data.Column

With Cells(standardRow, colNumber)
dblCompare = .Value
If Val(dblCompare) = 0 Then
MsgBox "Cannot determine standards. "
Exit Sub
End If
lngColor = .Interior.ColorIndex
End With

For Each datum In data
colNumber = datum.Column
If Val(datum.Value) dblCompare Then
With datum.Interior
'same color as standards cell
.ColorIndex = lngColor 'same color as standards cell
.Pattern = xlSolid
' .Pattern = xlGray16
.PatternColorIndex = xlAutomatic
End With
End If
Next datum
End Sub
--
Jim Cone
Portland, Oregon USA




"TomasC"
wrote in message
Howdy Folks,
I have a macro that will shade a cell if it exceeds a value contained in
another cell. For the most part, I use this to flag environmental chemistry
data if they exceed regulatory standards. It works well enough, but I'd like
to share this with others and it's a bit raw for that. Could someone point me
in a direction so that I could implement the following features:

1. If a cell does not contain a numeric value, it gets ignored. This is
pretty good, but I'd like to make it a little more sophisticated. If the cell
contains a less than sign (<), I'd like to ignore it. If a cell contains a
number and a text qualifier (such as 25 J) I would like the macro to compare
the numerical portion of the cell to a standard. The text value would always
come after the number and would usually have a space between them.
2. At present, if I want to shade a cell with something besides solid gray,
I have to uncomment a line in the code. Is there a way for a user to check a
box, or use a pulldown menu to select what color shading they'd like to use?

I realize these requests may be non-trivial, but if anyone has any hints or
resources, I would greatly appreciate them. Thanks in advance. My code is
shown below
~~~~~~~~~~~~~~~
Sub shadeExceed()
Dim data
Dim standardRow

Set data = Application.InputBox(prompt:="Select data range", Type:=8)
standardRow = Application.InputBox(prompt:="Enter letter of row that
contains the standards", Default:="3")


For Each datum In data
colnumber = datum.Column

If datum Cells(standardRow, colnumber) And IsNumeric(datum) =
True And Cells(standardRow, colnumber) < "" And IsNumeric(Cells(standardRow,
colnumber)) Then
With datum.Interior
.ColorIndex = 15 'light gray
'.ColorIndex = 37 'light blue
'.ColorIndex = 35 'light green
.Pattern = xlSolid
'.Pattern = xlGray16
.PatternColorIndex = xlAutomatic
End With
End If
Next datum
End Sub