Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONDITIONAL FORMATTING- EXCEL | Excel Discussion (Misc queries) | |||
Conditional Formatting in Excel | Excel Worksheet Functions | |||
conditional formatting in excel | New Users to Excel | |||
conditional formatting in excel | Excel Worksheet Functions | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |