I have a list of Diagnosis codes entered into 5 columns on a
worksheet. The second worksheet, named ICD9 Codes, lists all the
possible ICD9 Codes that are valid. I want to create a macro that you
could activate at the top of a column and it would validate each
number against the list in the second worksheet. If the value was
valid, it would set the color of the cell to green, if it was not
valid, it would set the color to red.
Here is the code I have so far.
Sub ValidateICD9()
'
' ValidateICD9 Macro
' Keyboard Shortcut: Ctrl+z
'
Dim iRow As Long
Dim rangeICD9 As Range
Dim rCell As Range
' Select Source Data Worksheet
Set rangeICD9 = Sheets("ICD9Codes").Range(Cells(2, "B"), Cells
(7000, "B").End(1))
Sheets("ICD9").Select
For Each rCell In Range(Cells(2, "W"), Cells(Rows.Count, "W").End
(xlUp))
If rangeICD9.Find(what:=rCell, LookIn:=xlValues,
LookAt:=xlWhole) Is Nothing Then
rCell.Font.ColorIndex = 3
Else
rCell.Font.ColorIndex = 3
End If
Next
End Sub
When I run this, I get a message that Subscript is out of range.
Right now its set for only column W. I don't know how to set this to
the active column.
Any help would be appreciated.
Thanks.
Rob Buonocore
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
