Colour Coding Concatenation?

[Mark McInerney]

Hi Folks - Thanks for having a look at this - appreciated.

Is there a way to concatenate two cells using concatenate(A2,B2) and to assign a colour to either value.

I've been trying concatenate([red]A2,B2) but it is not working.

Any help appreciated.

Thanks - Mark.

 

[Sergius]

We use conditional formatting.

Book1
	A	B	C
2	No	War	No War
Sheet2

Cell Formulas
Range	Formula
C2	C2	=A2&" "&B2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	Cell	does not contain a blank value	text	NO

 

[Mark McInerney]

Hi Sergius - Thanks for looking at this.

Is there a way to have the "No War" as follows "No War"

 

[Sergius]

Due to the fact that in the cell B2 the formula will not work.

 

[mumps]

I think you will need a macro to do what you want. Try:

Sub JoinData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, rng As Range, x As Long
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    ReDim arr(1 To UBound(v))
    For i = LBound(v) To UBound(v)
        arr(i) = v(i, 1) & " " & v(i, 2)
    Next i
    Range("C2").Resize(UBound(v)) = Application.Transpose(arr)
    For Each rng In Range("C2", Range("C" & Rows.Count).End(xlUp))
        x = InStr(1, rng.Value, " ", vbTextCompare)
        rng.Characters(1, x).Font.Color = vbRed
    Next rng
    Application.ScreenUpdating = True
End Sub