Hi Rob,
This problem is better solved with conditional formatting.
Select all the valid ICD9 codes on the second sheet, and name the range - let's
say, List
Then on 1st sheet, select all the cells you want to check, and any blank cells
that might have codes entered into them later.
Select Format, then Conditional Farmatting.
For Condition 1, select Formula is, then insert the following:
=COUNTIF(List,A1)>0
Replace the A1 ref with the ref of the first cell in your selection.
Set the font colour to green.
For condition 2, select Formula is, then insert the following:
=COUNTIF(List,A1)=0
Replace the A1 ref with the ref of the first cell in your selection, as above
Set the font colour to red.
Press OK.
This gives an instant validation of the data present, and any new data entered.
You could also use data validation to prevent new data being entered which is
not valid.
Regards - Dave.
> Date: Sat, 3 Jan 2009 13:14:08 -0800
> Subject: $$Excel-Macros$$ Validating a column of numbers against another
> worksheet
> From: robbuonoc...@infocore.us
> To: excel-macros@googlegroups.com
>
>
> 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
>
> >
_________________________________________________________________
Holiday cheer from Messenger. Download free emoticons today!
http://livelife.ninemsn.com.au/article.aspx?id=669758
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
