T
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
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