Dave:
Thanks for your response. I tried this solution but ran into
performance issues. There are 10 columns that hold values and
sometimes hundreds of rows of data. There are also approx. 20,000
valid ICD9 codes that this is validating against. Moving around the
spreadsheet caused serious delays.
Robby
On Jan 4, 1:58 am, Dave Bonallack <davebonall...@hotmail.com> wrote:
> 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- Hide quoted text
> -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
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
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
