Excel question re formats

How do I get a cell to equal not only the contents of another cell, but also the format? Under certain conditions, the cell can refer to another cell formatted as a date or a second cell formatted as a percentage.

I'm using Excel 2000, btw.

I would have thought either record a macro, write some VB, or if its only ever going to be either date or a percentage it might be easier to set up "conditional formatting", look through excel help for it. someone else might have a better answer though.

Can you specify a format in an "IF..." statement?

not in an normal function (afaik) but you can use conditional formatting for same, quick explanation here. Ive used it before for basic formatting like colours, bold, underline etc.

car said:

not in an normal function (afaik) but you can use conditional formatting for same, quick explanation here. Ive used it before for basic formatting like colours, bold, underline etc.

Click to expand...

It does not allow formatting of the number!!

I always use this forum for such questions:


You should get an answer pretty quickly.

Look up Cell in Help in Excel 2000

You can use the

=CELL("format",a1)

to get the type of formatting in use in the target cell

Invoke a small macro upon pressing enter on your original cell to change the format of the cell based on its value (ie in your case = A1) by using this macro together with the macro recorded or written to change for number format of the second cell. This is quite easy when you just want to update the number format of one cell but the first line will have to have a well written IF function when your looking at two or three cells and want to change the number format.


Private Sub cell_Change(ByVal Target As Range)

If A1 > 1 Or IsEmpty(Target) Then Exit Sub

If Target.Address = "C2" Then
If IsNumeric(Target) Then
On Error Resume Next
Application.EnableEvents = False
Target = recorded_macro_to_update_format
Application.EnableEvents = True
On Error GoTo 0
End If
End If

End Sub

This avoids having to remember if the cell is formatted later with a custom format, that you must recalculate the worksheet to update the CELL formula ie F9


or upgrade to Excell 2007 which offers conditional formating for number format