Keep formatting on an excel cell

Question

Basically I would like to keep the existing format of an Excel cell. If I use ActiveCell.Value2 = "new value";

The ActiveCell displays "new value" but loses the user defined formatting ( say red font, blue background), all formatting values goes back to Excel default format values.

Note: If you replace a cell value using Excel, Excel keeps the user's format. When recording this action as a macro, Excel uses the property FormulaR1C1 but according to MSDN this a property used for macros.

Answer 1

You may want to copy the cell and "Paste" the formatting back - http://www.ozgrid.com/forum/showthread.php?t=56324

Answer 2

If I use ActiveCell.Value2 = "new value" the ActiveCell displays "new value" but loses the user defined formatting

No, it does not, I just tested it (with Excel 2003). Opened a new empty document, formatted a column with some colors and user defined number format and and entered

ActiveCell.Value2="1"

in the immediate Window of the VBA editor. The existing format is kept intact. So if you have another scenario to deal with (where the format gets lost), please describe it in detail.

EDIT: if this behaviour is really different in VSTO, as a workaround, you can try to save the relevant format information of the ActiveCell before changing the value, for example

fci = ActiveCell.Range.Font.ColorIndex
ici = ActiveCell.Range.Interior.ColorIndex
pat = ActiveCell.Range.Interior.Pattern
nf = ActiveCell.Range.NumberFormat

then change the value

ActiveCell.Value2 = "new value"

and afterwards reassign the format info again

ActiveCell.Range.Font.ColorIndex = fci
ActiveCell.Range.Interior.ColorIndex = ici
ActiveCell.Range.Interior.Pattern = pat
ActiveCell.Range.NumberFormat = nf

(but beware, this is 'air code').