Formatting portions of a cell

Hi,

I was wondering (hoping) if there was a way to format text withing a cell --
below lists the formula that I have in the cell in question:

=CONCATENATE('Copy of Data for Graphs'!F15,": ",'Copy of Data for
Graphs'!J15," ",'2nd Level Sources & Conv'!F27,"
(",ROUND((E5/$E$89)*100,1),"%)")

What I'd like to do is the following:
- Apply a number format to the "'Copy of Data for Graphs'!J15" value so that
there are commas for every thousand (ie - so that 43456234 appears as
43,456,234)
- Apply a color to the font of "'Copy of Data for Graphs'!J15" so that it is
red while the font for the rest of the values in the cell remain black

I appreciate your help!

Thanks,

Robert

Cells that contain formulas and cells that are numeric don't support this kind
of formatting.

You could change it to values and do what you want, though.

Linking to specific cells in pivot table wrote:
>
> Hi,
>
> I was wondering (hoping) if there was a way to format text withing a cell --
> below lists the formula that I have in the cell in question:
>
> =CONCATENATE('Copy of Data for Graphs'!F15,": ",'Copy of Data for
> Graphs'!J15," ",'2nd Level Sources & Conv'!F27,"
> (",ROUND((E5/$E$89)*100,1),"%)")
>
> What I'd like to do is the following:
> - Apply a number format to the "'Copy of Data for Graphs'!J15" value so that
> there are commas for every thousand (ie - so that 43456234 appears as
> 43,456,234)
> - Apply a color to the font of "'Copy of Data for Graphs'!J15" so that it is
> red while the font for the rest of the values in the cell remain black
>
> I appreciate your help!
>
> Thanks,
>
> Robert

--

Dave Peterson

Could anyone give an example of what the code to do this would look like?
I've been trying to write a macro that turns just selected text within a cell
a specific color with no success. I think the code should look something
like this:

Sub MakeBlue()
'
' MakeBlue Macro
' Macro recorded 02/06/2006 by MWalker
'
Selection.Characters.Font.ColorIndex = 41
End Sub

but it's not working. Any suggestions? Do I need to reference the specific
characters somehow?

"Dave Peterson" wrote:

> Cells that contain formulas and cells that are numeric don't support this kind
> of formatting.
>
> You could change it to values and do what you want, though.
>
> Linking to specific cells in pivot table wrote:
> >
> > Hi,
> >
> > I was wondering (hoping) if there was a way to format text withing a cell --
> > below lists the formula that I have in the cell in question:
> >
> > =CONCATENATE('Copy of Data for Graphs'!F15,": ",'Copy of Data for
> > Graphs'!J15," ",'2nd Level Sources & Conv'!F27,"
> > (",ROUND((E5/$E$89)*100,1),"%)")
> >
> > What I'd like to do is the following:
> > - Apply a number format to the "'Copy of Data for Graphs'!J15" value so that
> > there are commas for every thousand (ie - so that 43456234 appears as
> > 43,456,234)
> > - Apply a color to the font of "'Copy of Data for Graphs'!J15" so that it is
> > red while the font for the rest of the values in the cell remain black
> >
> > I appreciate your help!
> >
> > Thanks,
> >
> > Robert
>
> --
>
> Dave Peterson
>

Selection.Characters(Start:=3,length:=5).Font.ColorIndex = 41

This does character 3 through 7 (5 characters).

michalaw wrote:
>
> Could anyone give an example of what the code to do this would look like?
> I've been trying to write a macro that turns just selected text within a cell
> a specific color with no success. I think the code should look something
> like this:
>
> Sub MakeBlue()
> '
> ' MakeBlue Macro
> ' Macro recorded 02/06/2006 by MWalker
> '
> Selection.Characters.Font.ColorIndex = 41
> End Sub
>
> but it's not working. Any suggestions? Do I need to reference the specific
> characters somehow?
>
> "Dave Peterson" wrote:
>
> > Cells that contain formulas and cells that are numeric don't support this kind
> > of formatting.
> >
> > You could change it to values and do what you want, though.
> >
> > Linking to specific cells in pivot table wrote:
> > >
> > > Hi,
> > >
> > > I was wondering (hoping) if there was a way to format text withing a cell --
> > > below lists the formula that I have in the cell in question:
> > >
> > > =CONCATENATE('Copy of Data for Graphs'!F15,": ",'Copy of Data for
> > > Graphs'!J15," ",'2nd Level Sources & Conv'!F27,"
> > > (",ROUND((E5/$E$89)*100,1),"%)")
> > >
> > > What I'd like to do is the following:
> > > - Apply a number format to the "'Copy of Data for Graphs'!J15" value so that
> > > there are commas for every thousand (ie - so that 43456234 appears as
> > > 43,456,234)
> > > - Apply a color to the font of "'Copy of Data for Graphs'!J15" so that it is
> > > red while the font for the rest of the values in the cell remain black
> > >
> > > I appreciate your help!
> > >
> > > Thanks,
> > >
> > > Robert
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

is it possible to format just a portion of the text in a cell without
referencing a specific character position? In the spreadsheet I'm
developing, I'd like to be able to highlight just a few words in a cell, and
hit a button to turn them a specific color. However, it won't be the same
word, or in the same place in the character string, in each cell. For
instance, we might want to color "dogs" in "Dogs are my favorite pet" in one
cell, and "Labrador" in "I have a Labrador" in the next.

"Dave Peterson" wrote:

> Selection.Characters(Start:=3,length:=5).Font.ColorIndex = 41
>
> This does character 3 through 7 (5 characters).
>
> michalaw wrote:
> >
> > Could anyone give an example of what the code to do this would look like?
> > I've been trying to write a macro that turns just selected text within a cell
> > a specific color with no success. I think the code should look something
> > like this:
> >
> > Sub MakeBlue()
> > '
> > ' MakeBlue Macro
> > ' Macro recorded 02/06/2006 by MWalker
> > '
> > Selection.Characters.Font.ColorIndex = 41
> > End Sub
> >
> > but it's not working. Any suggestions? Do I need to reference the specific
> > characters somehow?
> >
> > "Dave Peterson" wrote:
> >
> > > Cells that contain formulas and cells that are numeric don't support this kind
> > > of formatting.
> > >
> > > You could change it to values and do what you want, though.
> > >
> > > Linking to specific cells in pivot table wrote:
> > > >
> > > > Hi,
> > > >
> > > > I was wondering (hoping) if there was a way to format text withing a cell --
> > > > below lists the formula that I have in the cell in question:
> > > >
> > > > =CONCATENATE('Copy of Data for Graphs'!F15,": ",'Copy of Data for
> > > > Graphs'!J15," ",'2nd Level Sources & Conv'!F27,"
> > > > (",ROUND((E5/$E$89)*100,1),"%)")
> > > >
> > > > What I'd like to do is the following:
> > > > - Apply a number format to the "'Copy of Data for Graphs'!J15" value so that
> > > > there are commas for every thousand (ie - so that 43456234 appears as
> > > > 43,456,234)
> > > > - Apply a color to the font of "'Copy of Data for Graphs'!J15" so that it is
> > > > red while the font for the rest of the values in the cell remain black
> > > >
> > > > I appreciate your help!
> > > >
> > > > Thanks,
> > > >
> > > > Robert
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

If you know the characters you want to highlight, you can use that in your code:

Option Explicit
Sub testme01()

Dim myStrToHighlight As String
Dim StartPos As Long

myStrToHighlight = "labrador"

With ActiveSheet.Range("A1")
.Value = "I have a Labrador dog" 'just for testing
StartPos = InStr(1, .Value, myStrToHighlight, vbTextCompare)

If StartPos > 0 Then
.Characters(Start:=StartPos, Length:=Len(myStrToHighlight)) _
.Font.ColorIndex = 41
End If

End With

End Sub


michalaw wrote:
>
> is it possible to format just a portion of the text in a cell without
> referencing a specific character position? In the spreadsheet I'm
> developing, I'd like to be able to highlight just a few words in a cell, and
> hit a button to turn them a specific color. However, it won't be the same
> word, or in the same place in the character string, in each cell. For
> instance, we might want to color "dogs" in "Dogs are my favorite pet" in one
> cell, and "Labrador" in "I have a Labrador" in the next.
>
> "Dave Peterson" wrote:
>
> > Selection.Characters(Start:=3,length:=5).Font.ColorIndex = 41
> >
> > This does character 3 through 7 (5 characters).
> >
> > michalaw wrote:
> > >
> > > Could anyone give an example of what the code to do this would look like?
> > > I've been trying to write a macro that turns just selected text within a cell
> > > a specific color with no success. I think the code should look something
> > > like this:
> > >
> > > Sub MakeBlue()
> > > '
> > > ' MakeBlue Macro
> > > ' Macro recorded 02/06/2006 by MWalker
> > > '
> > > Selection.Characters.Font.ColorIndex = 41
> > > End Sub
> > >
> > > but it's not working. Any suggestions? Do I need to reference the specific
> > > characters somehow?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Cells that contain formulas and cells that are numeric don't support this kind
> > > > of formatting.
> > > >
> > > > You could change it to values and do what you want, though.
> > > >
> > > > Linking to specific cells in pivot table wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I was wondering (hoping) if there was a way to format text withing a cell --
> > > > > below lists the formula that I have in the cell in question:
> > > > >
> > > > > =CONCATENATE('Copy of Data for Graphs'!F15,": ",'Copy of Data for
> > > > > Graphs'!J15," ",'2nd Level Sources & Conv'!F27,"
> > > > > (",ROUND((E5/$E$89)*100,1),"%)")
> > > > >
> > > > > What I'd like to do is the following:
> > > > > - Apply a number format to the "'Copy of Data for Graphs'!J15" value so that
> > > > > there are commas for every thousand (ie - so that 43456234 appears as
> > > > > 43,456,234)
> > > > > - Apply a color to the font of "'Copy of Data for Graphs'!J15" so that it is
> > > > > red while the font for the rest of the values in the cell remain black
> > > > >
> > > > > I appreciate your help!
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Robert
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson