Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My macro does some of what I want, but I'd like it to do more
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
My macro does some of what I want, but I'd like it to do more
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
My macro does some of what I want, but I'd like it to do more
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
My macro does some of what I want, but I'd like it to do more
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |