Excel VBA - Conditional formatting
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA - Conditional formatting
 

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



"cata_and " wrote in message
...
Hi again,

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

= countif(E:E,E1)1

The problem appears when I use a VBA code to remove the blanks from the
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 conditional
formatting of the cells changes. It seems that the cells are no longer
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,
Catalin


---
Message posted from http://www.ExcelForum.com/

Excel VBA - Conditional formatting
 

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


"Bob Flanagan" wrote in message
...
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



"cata_and " wrote in message
...
Hi again,

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

= countif(E:E,E1)1

The problem appears when I use a VBA code to remove the blanks from the
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 conditional
formatting of the cells changes. It seems that the cells are no longer
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,
Catalin


---
Message posted from http://www.ExcelForum.com/