What you describe would require a very complex code to locate words of 30 or
more characters in cells containing multiple words. Almost the same thing
can be accomplished with the code below. I say almost because your list of
words and their standard abbreviations might not include all words that
exceed 30 characters in lenghth. But it will replace all the occurrences of
word that are in your list, provided the list of whole words are in a single
column and the abbreviations are in a single column and they are in the same
order. The code below uses an arbitrary range of A1:H30 which you can
change to the actual range to search. I chose Column Y for the whole words
and Column Z for the abbreviations and only use a limited number of words
for testing. I made the comparison case insensitive so you do not have to
worry about words being missed beacuse of capitalization, etc. There is a
way to include misspelled words but it is more trouble than it is worth, so
I avoided it for this exercise. This code goes in the public module1.

Sub abbrev()
Dim rng1 As Range, rng2 As Range, sh As Worksheet
Set sh = ActiveSheet
Set rng1 = sh.Range("Y2:Y7") '<<Change to actual
Set rng2 = sh.Range("Z2:Z7") '<<Change to actual
For i = 2 To rng1.Rows.Count + 1

'<<Change search range to actual. Change Col ref Y and Z. below

sh.Range("A1:M30").Replace What:=sh.Range("Y" & i).Value, _
Replacement:=sh.Range("Z" & i).Value, MatchCase:=False
Next
End Sub






"HelperBee" wrote in message
...
I need to search all the cells in a worksheet for values longer than 30
characters and shade those cells yellow.
For each of the yellow cells I need to compare the contents to see if they
contain words to be abbreviated. I have a two column worksheet where one
column has the search words and the other has the abbreviations.
I need to read each of the over 30 cells that were shaded and replace each
word in the abbreviation list with the abbreviated version.

Note: multiple words within a cell could need abbreviation, e.g. Insurance
Payment Invoice could be in the cell and get abbreviated to Insur Pymt
Inv,
only one word and abbreviation per cell in my abbreviation file.

Thoughts?

I've used the conditional formatting in 2007 which works great for finding
the 30 characters and shading them, but when it comes to the abbreviations
I've been stuck with manual comparison.

Help appreciated, dozens of files to apply this to.

Thanks.