My macro does some of what I want, but I'd like it to do more

[TomasC]

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

 

[Jim Cone]

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

 

[TomasC]

Howdy Jim,

Thank you for your help. That definitely gets me going on the right track. I
just did a little test and it deals nicely with qualified data. I like the
error handling in there, too. One question I have refers to this line:

.ColorIndex = lngColor 'same color as standards cell

What is your reason for shading things the same color as the standards cell?
Does the macro assume that I will shade the standard row first? Whatever the
case, many thanks. This is very cool.

TomasC

 

[Jim Cone]

TomasC,
You are welcome.
--
Yes, the macro assumes you have shaded the standards row/cells.
That eliminates having to ask the user for a color preference.
And in my opinion, for what it is worth, using more than two or three
colors on a spreadsheet reduces its effectiveness.
--
Jim Cone
Portland, Oregon USA



"TomasC"
wrote in message
Howdy Jim,
Thank you for your help. That definitely gets me going on the right track. I
just did a little test and it deals nicely with qualified data. I like the
error handling in there, too. One question I have refers to this line:

.ColorIndex = lngColor 'same color as standards cell

What is your reason for shading things the same color as the standards cell?
Does the macro assume that I will shade the standard row first?
Whatever the case, many thanks. This is very cool.
TomasC