Hello,
With the function concatenate, how can we
A) Ask to get the same format as the original cell
B) Force a color format, like red
Exemple = concatenate(A1; " "; A2; " "; A3)
I would like that
1) A1 displays the same color and number format as the cell A1 (for example
green with number #'##0.00
2) Force the format of A2 in red

I know that the function TEXT exists
=concatenate(TEXT (A1;"#'##0.00"); etc.)
But how can I say "red" or "same format as cell A1" ?
Thank you for all advise

See the help file on colorIndex for more info but basically its

Worksheets("Sheet1").Range("D19").Font.ColorIndex = 3


"Thierry" wrote in message
...
Hello,
With the function concatenate, how can we
A) Ask to get the same format as the original cell
B) Force a color format, like red
Exemple = concatenate(A1; " "; A2; " "; A3)
I would like that
1) A1 displays the same color and number format as the cell A1 (for
example green with number #'##0.00
2) Force the format of A2 in red

I know that the function TEXT exists
=concatenate(TEXT (A1;"#'##0.00"); etc.)
But how can I say "red" or "same format as cell A1" ?
Thank you for all advise

A formula cannot be used to modifiy the format of a cell, it just
returns a value.

Excel (2002/2003) will allow you to part format the font of a cell if it
contains text but not if it contains a formula. So if you want your
result to be dynamic and keep the formula in the cell I don't believe
you can have different font colours.

If you are happy to replace the formula with its result in text format
then you could do it with a macro like this (select the cell containing
the formula first):

Sub FText()
Dim Prec As Range
Dim cell As Range
Dim TLen As Integer
Set Prec = ActiveCell.DirectPrecedents
TLen = 1
With ActiveCell
.NumberFormat = "@"
.Value = .Value
End With
For Each cell In Prec
ActiveCell.Characters(TLen, Len(cell.Text)).Font.ColorIndex = _
cell.Font.ColorIndex
TLen = TLen + Len(cell.Text)
Next cell
End Sub


Hope this helps
Rowan


Thierry wrote:
Hello,
With the function concatenate, how can we
A) Ask to get the same format as the original cell
B) Force a color format, like red
Exemple = concatenate(A1; " "; A2; " "; A3)
I would like that
1) A1 displays the same color and number format as the cell A1 (for example
green with number #'##0.00
2) Force the format of A2 in red

I know that the function TEXT exists
=concatenate(TEXT (A1;"#'##0.00"); etc.)
But how can I say "red" or "same format as cell A1" ?
Thank you for all advise

Concatenate only deals with the values of the cells and not with the formats
applied to those cells. In order to concatenate and apply colour would
require a bunch of code (assuming you want different formats for each cell
being concatenated). Otherwise just format the cell with the concatenate
formula.
--
HTH...

Jim Thomlinson


"Thierry" wrote:

Hello,
With the function concatenate, how can we
A) Ask to get the same format as the original cell
B) Force a color format, like red
Exemple = concatenate(A1; " "; A2; " "; A3)
I would like that
1) A1 displays the same color and number format as the cell A1 (for example
green with number #'##0.00
2) Force the format of A2 in red

I know that the function TEXT exists
=concatenate(TEXT (A1;"#'##0.00"); etc.)
But how can I say "red" or "same format as cell A1" ?
Thank you for all advise