On May 13, 8:53*am, Matthew Herbert
wrote:
HelperBee,

Here is some code that should at least give you an idea of how this can
work. *The code runs on a selection of cells. *Since you didn't provide
example data, I made some assumptions. *(For example, I don't know what you
mean by "one word and abbreviation per cell").

Best,

Matthew Herbert

Sub FindLengthAndReplace()
Dim Rng As Range
Dim rngCell As Range

Set Rng = Selection

'loop each cell in the Selection
For Each rngCell In Rng.Cells
* * With rngCell
* * * * 'If the character length of the value within the cell
* * * * ' * is greater than or equal to 30, then color the
* * * * ' * cell and perform the desired replacements specified
* * * * ' * below. *(If no replacements are found, nothing
* * * * ' * happens).
* * * * If Len(.Value) = 30 Then
* * * * * * .Interior.ColorIndex = 16 'use a desired number here
* * * * * * .Value = Replace(.Value, "Insurance", "Insur")
* * * * * * .Value = Replace(.Value, "Payment", "Pymt")
* * * * * * .Value = Replace(.Value, "Invoice", "Inv")
* * * * End If
* * End With
Next rngCell
End Sub



"HelperBee" wrote:
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.- Hide quoted text -

- Show quoted text -

Have you tried with autocorrect feature for replacement of text.

You can use
Application.Autocorrect.AddReplacement What As String, Replacement As
String
for adding your replacement table.