I have a macro that loops through a range of cells. It takes the text
in the cell, determines the appropriate validation group (a range of
cells on another spreadsheet) and compares it to that range. If it
finds the value, it changes the color to blue, if it doesn't find the
value in the list, then it changes the color to red.
The code works fine except for one thing. I can't always guarantee
that the cell has text entered into it. Sometimes it get entered as a
number. Either way, I want my code to treat the value as text. My
code is listed below. The variable that is a problem is rCell. I
need it to be treated as text. Does anyone see something I'm missing?
Sub ValidateICD9()
'
' ValidateICD9 Macro
'
' Keyboard Shortcut: Ctrl+z
'
Dim iRow As Long
Dim rangeICD9 As Range
Dim rCell As Range
Dim rRow As Range
Dim searchYear As String
...not relevant code
For Each rCell In Range(ActiveCell, ActiveCell.End(xlToRight))
Set rangeICD9 = Worksheets("ICD9-Codes").Range
(searchYear)
If IsDate(rCell) = True Then
'Do nothing
ElseIf rangeICD9.Find(what:=rCell, LookIn:=xlValues,
LookAt:=xlWhole) Is Nothing Then
rCell.Font.ColorIndex = 3
Else
rCell.Font.ColorIndex = 14
End If
Next
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(1, 0).Select
Loop
End Sub
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
