The real bad news is that if you don't keep track of the formatting (font, font
color, super/sub script, strikethrough, ...) on a character by character basis,
you'll end up with the formatting attribute for the first character (not the
default, IIRC).

Rick Rothstein wrote:

Your posting raised made me realize my code needed to be modified. Why? I
didn't take into account that other characters may be colored with colors
other than white or the default (usually black)... my prior code makes every
character the default color (usually black). Here is modified code which
preserves the existing non-white character colors while deleting the white
characters...

Sub DeleteWhiteCharacters()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim Chars As String
Dim Colors() As String
On Error GoTo Whoops
Application.ScreenUpdating = False
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
Chars = .Cells(X, "A").Text
ReDim Colors(1 To Len(Chars))
For Z = 1 To Len(Chars)
If .Cells(X, "A").Characters(Z, 1).Font.ColorIndex = 2 Then
Mid(Chars, Z, 1) = Chr(1)
Colors(Z) = "XX"
Else
If .Cells(X, "A").Characters(Z, 1).Font.ColorIndex < 0 Then
Colors(Z) = "00"
Else
Colors(Z) = Format(.Cells(X, "A").Characters(Z,
1).Font.ColorIndex, "00")
End If
End If
Next
.Cells(X, "A").ClearContents
.Cells(X, "A").Value = Replace(Chars, Chr(1), "")
Colors =
Split(Application.WorksheetFunction.Trim(Replace(J oin(Colors), "XX", "")))
For Z = 1 To UBound(Colors)
If Colors(Z - 1) < "00" Then
.Cells(X, "A").Characters(Z, 1).Font.ColorIndex = CLng(Colors(Z -
1))
End If
Next
Next
End With
Whoops:
Application.ScreenUpdating = True
End Sub

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
It didn't work for me in xl2003.

But I figured I did something wrong.

It always came back that it couldn't find any matches to change.

I used asdfqwerasdf in a bunch of cells with qwer in white and both a&f's
automatic and s&d's in red.



Ron Rosenfeld wrote:

On Sat, 3 Jan 2009 15:09:38 +0100, "Petr Danes"
wrote:

Excel also has the FindFormat method in VBA and the format option in the
GUI
Find/Replace dialog. Either will probably be a good deal faster then
manually looping through each cell in 30,000 rows.

Pete

And what happened when you tested your suggestions against a cell in
which just
a few letters were differentially formatted, as the OP requested?
--ron

--

Dave Peterson

--

Dave Peterson