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

Formatting portions of a cell
 

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

Formatting portions of a cell
 

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

Formatting portions of a cell
 

Selection.Characters(Start:=3,length:=5).Font.Colo rIndex = 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

Formatting portions of a cell
 

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.Colo rIndex = 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

Formatting portions of a cell
 

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.Colo rIndex = 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

Formatting portions of a cell
 

Right, but what if I don't know the characters that need to be colored? In
my spreadsheet of 42 observations, I could potentially have 42 different
words that I would want change the color of. Is there a way to write the
code so that whatever characters are selected are formatted? An example of
the process I want would be to click in cell, highlight any of the words
within that cell, and use a shortcut key to run the macro.

"Dave Peterson" wrote:

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.Colo rIndex = 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

Formatting portions of a cell
 

Macros won't run when you're editing the cell.

If you have a list of words you know you want to highlight, you could use a
macro to cycle through all the words:

Option Explicit
Option Compare Text
Sub testme()

Application.ScreenUpdating = False

Dim myWords As Variant
Dim myRng As Range
Dim foundCell As Range
Dim iCtr As Long 'word counter
Dim cCtr As Long 'character counter
Dim FirstAddress As String
Dim AllFoundCells As Range
Dim myCell As Range

'add other words here
myWords = Array("widgets","assemblies","another","word","her e")

Set myRng = Selection

On Error Resume Next
Set myRng = Intersect(myRng, _
myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please choose a range that contains text constants!"
Exit Sub
End If

For iCtr = LBound(myWords) To UBound(myWords)
FirstAddress = ""
Set foundCell = Nothing
With myRng
Set foundCell = .Find(what:=myWords(iCtr), _
LookIn:=xlValues, lookat:=xlPart, _
after:=.Cells(.Cells.Count))

If foundCell Is Nothing Then
MsgBox myWords(iCtr) & " wasn't found!"
Else
Set AllFoundCells = foundCell
FirstAddress = foundCell.Address
Do
If AllFoundCells Is Nothing Then
Set AllFoundCells = foundCell
Else
Set AllFoundCells = Union(foundCell, AllFoundCells)
End If
Set foundCell = .FindNext(foundCell)

Loop While Not foundCell Is Nothing _
And foundCell.Address < FirstAddress
End If

End With

If AllFoundCells Is Nothing Then
'do nothing
Else
For Each myCell In AllFoundCells.Cells
For cCtr = 1 To Len(myCell.Value)
If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _
= myWords(iCtr) Then
myCell.Characters(Start:=cCtr, _
Length:=Len(myWords(iCtr))) _
.Font.colorindex = 3
End If
Next cCtr
Next myCell
End If
Next iCtr
Application.ScreenUpdating = True

End Sub

This line:
myCell.Characters(Start:=cCtr, _
Length:=Len(myWords(iCtr))) _
.Font.colorindex = 3
changes the color.



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

michalaw wrote:

Right, but what if I don't know the characters that need to be colored? In
my spreadsheet of 42 observations, I could potentially have 42 different
words that I would want change the color of. Is there a way to write the
code so that whatever characters are selected are formatted? An example of
the process I want would be to click in cell, highlight any of the words
within that cell, and use a shortcut key to run the macro.

"Dave Peterson" wrote:

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.Colo rIndex = 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


--

Dave Peterson

Formatting portions of a cell
 

Thanks for all your help!

"Dave Peterson" wrote:

Macros won't run when you're editing the cell.

If you have a list of words you know you want to highlight, you could use a
macro to cycle through all the words:

Option Explicit
Option Compare Text
Sub testme()

Application.ScreenUpdating = False

Dim myWords As Variant
Dim myRng As Range
Dim foundCell As Range
Dim iCtr As Long 'word counter
Dim cCtr As Long 'character counter
Dim FirstAddress As String
Dim AllFoundCells As Range
Dim myCell As Range

'add other words here
myWords = Array("widgets","assemblies","another","word","her e")

Set myRng = Selection

On Error Resume Next
Set myRng = Intersect(myRng, _
myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please choose a range that contains text constants!"
Exit Sub
End If

For iCtr = LBound(myWords) To UBound(myWords)
FirstAddress = ""
Set foundCell = Nothing
With myRng
Set foundCell = .Find(what:=myWords(iCtr), _
LookIn:=xlValues, lookat:=xlPart, _
after:=.Cells(.Cells.Count))

If foundCell Is Nothing Then
MsgBox myWords(iCtr) & " wasn't found!"
Else
Set AllFoundCells = foundCell
FirstAddress = foundCell.Address
Do
If AllFoundCells Is Nothing Then
Set AllFoundCells = foundCell
Else
Set AllFoundCells = Union(foundCell, AllFoundCells)
End If
Set foundCell = .FindNext(foundCell)

Loop While Not foundCell Is Nothing _
And foundCell.Address < FirstAddress
End If

End With

If AllFoundCells Is Nothing Then
'do nothing
Else
For Each myCell In AllFoundCells.Cells
For cCtr = 1 To Len(myCell.Value)
If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _
= myWords(iCtr) Then
myCell.Characters(Start:=cCtr, _
Length:=Len(myWords(iCtr))) _
.Font.colorindex = 3
End If
Next cCtr
Next myCell
End If
Next iCtr
Application.ScreenUpdating = True

End Sub

This line:
myCell.Characters(Start:=cCtr, _
Length:=Len(myWords(iCtr))) _
.Font.colorindex = 3
changes the color.



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

michalaw wrote:

Right, but what if I don't know the characters that need to be colored? In
my spreadsheet of 42 observations, I could potentially have 42 different
words that I would want change the color of. Is there a way to write the
code so that whatever characters are selected are formatted? An example of
the process I want would be to click in cell, highlight any of the words
within that cell, and use a shortcut key to run the macro.

"Dave Peterson" wrote:

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.Colo rIndex = 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


--

Dave Peterson