Excel VBA - Conditional formatting

[cata_and]

Hi again,

I have a column with data (including some empty cells as well) in m
spreadsheet with a conditional formatting with the following conditio
for tracing the duplicates:

= countif(E:E,E1)>1

The problem appears when I use a VBA code to remove the blanks from th
cells of this column

For each Cell in Rng
Cell.Value = Trim(Cell.Value)
Next

After that, for the empty cells in the column the conditiona
formatting of the cells changes. It seems that the cells are no longe
empty. I tested with Isempty(cell.value) and the result is true.

So, please help me to remove the blanks and keep the formatting.

Thank you and regards,
Catali

 

[Bob Flanagan]

Try

If trim(cell.value)="" then
cell.clear
else
Cell.Value = Trim(Cell.Value)
end if

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Tom Ogilvy]

Clear will remove formatting (including conditional formatting), so I
suggest you use cell.Clearcontents instead:

If trim(cell.value)="" then
cell.Clearcontents
else
Cell.Value = Trim(Cell.Value)
end if

--
Regards,
Tom Ogilvy

Try

If trim(cell.value)="" then
cell.clear
else
Cell.Value = Trim(Cell.Value)
end if

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel