concatenate + format color

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>