That is the reverse of how I perceived your problem. Performing this is
more complex and (as far as I know) cannot be done with conditional
formatting. You will need to use VBA.
Right-click on the tab or the worksheet in which you need the highlighting
work performed.
Copy and past the code below my sig (everything between asterisks).
Some instructions for use:
1) Where you see highRiskRange = "high" in the code below, you can change
"High" to the range of cells or named range of your high risk zip codes.
Just make sure the quote marks are retained with any different name.
Examples:
highRiskRange = "Z1:Z20"
highRiskRange = "highRiskZips"
2) Where you see zipsToCheck = "A:A" in the code below, you can change
"A:A" in a similar manner to pick up the range where zips are entered. For
example, to pick up column C
zipsToCheck = "C:C"
3) Highlighting is currently set to light yellow. To choose a different
colour for you highlighting find the colour you like under 'interior' in
the table on the following web page.
http://www.mvps.org/dmcritchie/excel/colors.htm
After finding the number of the colour of your choice, change the line
myColor = 36 to myColor = {your chosen number}. Example, if you like red
change it to
myColor = 3 'red
4) I set a minimum high risk zip code length of five characters. If you
need to pick up shorter target zips, change the following line
minZipLength = 5
5) If after pasting, any of the code is showing in red font, it means that
text wrapping went awry. Just go to the end of the line above the red text
and press [delete]
Notes
Anything in a VBA line following a single quote is treated as comment so,
you could just change it to
myColor = 3
Changing cell colour effect border colour so, I did some border work in
the code to approximate standard Excel visuals.
For efficiency, the code is set to exit if there is a multi-cell change.
This is mainly to allow for deletions but, it also prevents checking if
the fill-handle is used.
I ran up the code relatively quickly, testing it on a small range of
scenarios. If you strike problems or need explanations, feel free to ask.
HTH
--
Steve
'*****************************************
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c
Dim isect
Dim tgtAddr
Dim zipsToCheck
Dim highRiskRange
Dim myColor
Dim minZipLength
minZipLength = 5 'the minimum length of high risk zips
myColor = 36 ' light yellow
highRiskRange = "high"
zipsToCheck = "A:A" 'set to look at column A.
'Change to your column
'or your named range
tgtAddr = Target.Address 'Do not change
Set isect = Application.Intersect(Range(zipsToCheck), Range(tgtAddr))
If isect Is Nothing Then
'Value not in column A
Else
'format if in high list
Range(tgtAddr).Interior.ColorIndex = xlNone 'remove existing
interior color
On Error GoTo Worksheet_Change_Exit ' exits on multi-cell
changes
' such as the fill handle
If Range(tgtAddr).Value & "" <> "" And Len(Range(tgtAddr).Value) >=
minZipLength Then
For Each c In Range(highRiskRange)
If InStr(c.Value, Range(tgtAddr).Value) > 0 Then
With Range(tgtAddr)
.Interior.ColorIndex = myColor
End With
With Range(tgtAddr).Borders()
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
Exit Sub
End If
Next c
End If
End If
Worksheet_Change_Exit:
End Sub
'*****************************************
Diana said:
This is very confusing. : (
Here is an example:
A B
Zip HighRisk Zips
80302 80302
803025698 91568
91056 78595
91568
71485
62185
94851694
785951561
184860
So I know I would Highlight the High Risk Zips, label them HIGH in the
name
box. Then, use conditional formatting for zip =COUNTIF(HIGH,A2) and have
them all turn blue. My problem is that for example, in A2 there is the
9-digit zip code, and in the HIGH colunm it is only 5 digits.
The last example you gave me to help, I would add to the conditional
formatting of the zip with the =COUNTIF. Is this wrong? Because when I
do,
all the numbers end up turning blue, therefore seems not to be working.
Please clarify. Thank you so much for your help. I really appreciate
it.
700