How to set the font color returned by lookup in a cell ?

The formula in a cell is :
=IF(B2=0,"","Tel : "&VLOOKUP(B8, A23:B41, 2,FALSE))

What I expect is, the word "Tel:" is red and the number returned by lookup
is blue and the cell remaining blank if no data in cell B2.

Thanks,

Hi,

It cannot be done with formulas but with VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")
With Target.Characters(Start:=1, Length:=4).Font
.ColorIndex = 3
End With
With Target.Characters(Start:=5, Length:=12).Font
.ColorIndex = 5
End With
End Sub

HTH

On Jan 5, 7:14 am, Carim wrote:
Hi,

It cannot be done with formulas but with VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")
WithTarget.Characters(Start:=1, Length:=4).Font
.ColorIndex = 3
End With
WithTarget.Characters(Start:=5, Length:=12).Font
.ColorIndex = 5
End With
End Sub

HTH

Hi Carim,
I'm using your code and it works great for a cell with a text but if
there is in it mixed text and formula it does not seem to work.
What I'm doing wrong?

Bye, Stefano.

You very definitely want to avoid messing with the formulas. You can use the
HasFormula property of a Range to see if there is a formula and get out if
there is one. Also, you probably want to exit if Target has more than one
cell:

If Target.Cells.Count 1 Then
Exit Sub
End If

If Target.HasFormula = True Then
Exit Sub
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)



wrote in message
...
On Jan 5, 7:14 am, Carim wrote:
Hi,

It cannot be done with formulas but with VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")
WithTarget.Characters(Start:=1, Length:=4).Font
.ColorIndex = 3
End With
WithTarget.Characters(Start:=5, Length:=12).Font
.ColorIndex = 5
End With
End Sub

HTH

Hi Carim,
I'm using your code and it works great for a cell with a text but if
there is in it mixed text and formula it does not seem to work.
What I'm doing wrong?

Bye, Stefano.

On Jan 6, 6:07 pm, "Chip Pearson" wrote:
You very definitely want to avoid messing with the formulas. You can use the
HasFormula property of a Range to see if there is a formula and get out if
there is one. Also, you probably want to exit if Target has more than one
cell:

If Target.Cells.Count 1 Then
Exit Sub
End If

If Target.HasFormula = True Then
Exit Sub
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consultingwww.cpearson.com
(email on the web site)

wrote in message

...

On Jan 5, 7:14 am, Carim wrote:
Hi,

It cannot be done with formulas but with VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")
WithTarget.Characters(Start:=1, Length:=4).Font
.ColorIndex = 3
End With
WithTarget.Characters(Start:=5, Length:=12).Font
.ColorIndex = 5
End With
End Sub

HTH

Hi Carim,
I'm using your code and it works great for a cell with a text but if
there is in it mixed text and formula it does not seem to work.
What I'm doing wrong?

Bye, Stefano.

Sorry, I'm not sure to understand what I have to do. Where I need to
insert the two If statement?

The code would go within your existing Worksheet_Change procedure. For
example,

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then
Exit Sub
End If

If Target.HasFormula = True Then
Exit Sub
End If

'''''''''''''''''''''''''''''''''''''''''''''
' Your existing code goes here.
'''''''''''''''''''''''''''''''''''''''''''''

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


wrote in message
...
On Jan 6, 6:07 pm, "Chip Pearson" wrote:
You very definitely want to avoid messing with the formulas. You can use
the
HasFormula property of a Range to see if there is a formula and get out
if
there is one. Also, you probably want to exit if Target has more than one
cell:

If Target.Cells.Count 1 Then
Exit Sub
End If

If Target.HasFormula = True Then
Exit Sub
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consultingwww.cpearson.com
(email on the web site)

wrote in message

...

On Jan 5, 7:14 am, Carim wrote:
Hi,

It cannot be done with formulas but with VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")
WithTarget.Characters(Start:=1, Length:=4).Font
.ColorIndex = 3
End With
WithTarget.Characters(Start:=5, Length:=12).Font
.ColorIndex = 5
End With
End Sub

HTH

Hi Carim,
I'm using your code and it works great for a cell with a text but if
there is in it mixed text and formula it does not seem to work.
What I'm doing wrong?

Bye, Stefano.

Sorry, I'm not sure to understand what I have to do. Where I need to
insert the two If statement?

You are doing nothing wrong except for wanting something that Excel cannot
provide.

You cannot format for different fonts in a cell that has a formula.

Carim's macro assumes the cell(s) contain text only.


Gord Dibben MS Excel MVP

On Sun, 6 Jan 2008 07:43:52 -0800 (PST), wrote:

On Jan 5, 7:14 am, Carim wrote:
Hi,

It cannot be done with formulas but with VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")
WithTarget.Characters(Start:=1, Length:=4).Font
.ColorIndex = 3
End With
WithTarget.Characters(Start:=5, Length:=12).Font
.ColorIndex = 5
End With
End Sub

HTH

Hi Carim,
I'm using your code and it works great for a cell with a text but if
there is in it mixed text and formula it does not seem to work.
What I'm doing wrong?

Bye, Stefano.

On Jan 6, 6:31 pm, Gord Dibben <gorddibbATshawDOTca wrote:
You are doing nothing wrong except for wanting something that Excel cannot
provide.

You cannot format for different fonts in a cell that has a formula.

Carim's macro assumes the cell(s) contain text only.

Gord Dibben MS Excel MVP

On Sun, 6 Jan 2008 07:43:52 -0800 (PST), wrote:
On Jan 5, 7:14 am, Carim wrote:
Hi,

It cannot be done with formulas but with VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")
WithTarget.Characters(Start:=1, Length:=4).Font
.ColorIndex = 3
End With
WithTarget.Characters(Start:=5, Length:=12).Font
.ColorIndex = 5
End With
End Sub

HTH

Hi Carim,
I'm using your code and it works great for a cell with a text but if
there is in it mixed text and formula it does not seem to work.
What I'm doing wrong?

Bye, Stefano.

Right, I didn't known that, but reading Carim's answer I thought it
was possible to do.
Many thank the same.
Bye,Stefano.

Hi,

You can't really do that with a formula, but you can fake it.

If your formula is in cell B6 then enter =LEFT(B6,3) in cell E6 and
=RIGHT(B6,6) in F6. Format E6 to red and F6 to blue. Resize the columns so
they "appear" directly next to each other. Turn of gridlines or change the
cell borders to white.

If that isn't good enough you can select E6:F6 and clicking the Camera tool,
then clicking a cell where you want to see the result. With the picture
selected choose Format, Picture, Colors and Lines, Line Color, No Line.
Resize the cell where picture is so the cell is identical in size to the
picture.


--
Cheers,
Shane Devenshire


"yclhk" wrote:

How to set the font color returned by lookup in a cell ?

The formula in a cell is :
=IF(B2=0,"","Tel : "&VLOOKUP(B8, A23:B41, 2,FALSE))

What I expect is, the word "Tel:" is red and the number returned by lookup
is blue and the cell remaining blank if no data in cell B2.

Thanks,

Nice solution Shane.

yclhk

why can't you use 2 cells next to each other?


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"ShaneDevenshire" wrote:

Hi,

You can't really do that with a formula, but you can fake it.

If your formula is in cell B6 then enter =LEFT(B6,3) in cell E6 and
=RIGHT(B6,6) in F6. Format E6 to red and F6 to blue. Resize the columns so
they "appear" directly next to each other. Turn of gridlines or change the
cell borders to white.

If that isn't good enough you can select E6:F6 and clicking the Camera tool,
then clicking a cell where you want to see the result. With the picture
selected choose Format, Picture, Colors and Lines, Line Color, No Line.
Resize the cell where picture is so the cell is identical in size to the
picture.


--
Cheers,
Shane Devenshire


"yclhk" wrote:

How to set the font color returned by lookup in a cell ?

The formula in a cell is :
=IF(B2=0,"","Tel : "&VLOOKUP(B8, A23:B41, 2,FALSE))

What I expect is, the word "Tel:" is red and the number returned by lookup
is blue and the cell remaining blank if no data in cell B2.

Thanks,