VBA part of cell content

[Jack Sons]

Hi all,

I want to do something (change font size and font color f.i.) with the part
of the content of the active cell that is between the second and the third
chr(10). What code do I need?

I thank you in advance for your advice.

Jack Sons
The Netherlands

 

[CLR]

This might get you started.......modify as needed
Sub HighlightCharacter()
With ActiveCell.Characters(Start:=3, Length:=2).Font
.FontStyle = "Bold"
.Size = 14
.ColorIndex = 3
End With
End Sub

Vaya con Dios,
Chuck, CABGx3

 

[Chip Pearson]

You can modify static text in a cell with code like

Range("A1").Characters(5, 3).Font.Bold = True

This turns characters 5, 6, and 7 bold.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Jack Sons]

Thank you Chip.

Problem is, I don't know on what posititions the second chr(10) and the
third chr(10) are. (with chr(10) I mean the character that causes the
content to go on at a new line in the cell) Their positions vary from cell
to cell in the column. The content is like
xxxx...xxxchr(10)xx...xxxxchr(10)xxxxxx...xxchr(10) xxxx etc. The x's stand
for other characters. The number of x's vary from cell to cell. In this
exaple I want to do things wits the xx...xxxx part because it is between
the second chr(10) and the third chr(10). What is the way to do this with
code?

Jack.

You can modify static text in a cell with code like

Range("A1").Characters(5, 3).Font.Bold = True

This turns characters 5, 6, and 7 bold.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Hi all,

I want to do something (change font size and font color f.i.) with the
part
of the content of the active cell that is between the second and the third
chr(10). What code do I need?

I thank you in advance for your advice.

Jack Sons
The Netherlands

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim FirstPos As Long
Dim SecondPos As Long
Dim myCell As Range

Set myCell = ActiveSheet.Range("A1")

FirstPos = InStr(1, myCell.Value, vbLf, vbTextCompare)
SecondPos = InStr(FirstPos + 1, myCell.Value, vbLf, vbTextCompare)

myCell.Characters(FirstPos + 1, SecondPos - FirstPos - 1).Font.Bold = True

End Sub

Excel's VBA is pretty forgiving. It didn't even care if that length portion was
greater than 0.

Thank you Chip.

Problem is, I don't know on what posititions the second chr(10) and the
third chr(10) are. (with chr(10) I mean the character that causes the
content to go on at a new line in the cell) Their positions vary from cell
to cell in the column. The content is like
xxxx...xxxchr(10)xx...xxxxchr(10)xxxxxx...xxchr(10) xxxx etc. The x's stand
for other characters. The number of x's vary from cell to cell. In this
exaple I want to do things wits the xx...xxxx part because it is between
the second chr(10) and the third chr(10). What is the way to do this with
code?

Jack.

You can modify static text in a cell with code like

Range("A1").Characters(5, 3).Font.Bold = True

This turns characters 5, 6, and 7 bold.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Hi all,

I want to do something (change font size and font color f.i.) with the
part
of the content of the active cell that is between the second and the third
chr(10). What code do I need?

I thank you in advance for your advice.

Jack Sons
The Netherlands

 

[Jack Sons]

Thanks Dave.

I did my last posting at 02.05 AM, so I went to bed and saw your answer
just now. Once in bed I realized that I could have explained myself much
better by simply saying that I wanted to do something with the third line in
the cell, but I was focused too much at the cornerstones of the third line,
the chr(10)'s. These are needed of course to determine what's on that third
line. Stupid that I didn't think of an InStr in an InStr. I'm sorry, too
late at night I guess.

Question 1:
Is the number of possible "nested" InStr's unlimited?

Question 2:
You wrote "... if that length portion was greater than 0". Did you mean
wether the third line contains any characters - other than (the third)
chr(10)?

Jack.

Option Explicit
Sub testme()

Dim FirstPos As Long
Dim SecondPos As Long
Dim myCell As Range

Set myCell = ActiveSheet.Range("A1")

FirstPos = InStr(1, myCell.Value, vbLf, vbTextCompare)
SecondPos = InStr(FirstPos + 1, myCell.Value, vbLf, vbTextCompare)

myCell.Characters(FirstPos + 1, SecondPos - FirstPos - 1).Font.Bold =
True

End Sub

Excel's VBA is pretty forgiving. It didn't even care if that length
portion was
greater than 0.

Thank you Chip.

Problem is, I don't know on what posititions the second chr(10) and the
third chr(10) are. (with chr(10) I mean the character that causes the
content to go on at a new line in the cell) Their positions vary from
cell to cell in the column. The content is like
xxxx...xxxchr(10)xx...xxxxchr(10)xxxxxx...xxchr(10) xxxx etc. The x's
stand for other characters. The number of x's vary from cell to cell. In
this exaple I want to do things wits the xx...xxxx part because it is
between the second chr(10) and the third chr(10). What is the way to do
this with code?

Jack.

You can modify static text in a cell with code like

Range("A1").Characters(5, 3).Font.Bold = True

This turns characters 5, 6, and 7 bold.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



Hi all,

I want to do something (change font size and font color f.i.) with the
part
of the content of the active cell that is between the second and the
third
chr(10). What code do I need?

I thank you in advance for your advice.

Jack Sons
The Netherlands

 

[JLatham]

#1 - not sure what you mean about "nested" Instr() limits. But I think that
the answer is no - you can nest them pretty deep in one formula, until the
formula either becomes so complex that the VB engine cannot evaluate it, or
until you run out of stack space.
For example, you could do away with FirstPos and SecondPos entirely in
Dave's code and use this (hard to decipher later) line:
myCell.Characters(InStr(1, myCell.Value, vbLf, vbTextCompare) + 1, _
InStr(InStr(1, myCell.Value, vbLf, vbTextCompare) + 1, _
myCell.Value, vbLf, vbTextCompare) - _
InStr(1, myCell.Value, vbLf, vbTextCompare) - 1).Font.Bold = True
If that's what you mean by nesting InStr()s

For #2 - even if the cell is empty, it will not throw an error, in that case
it basically sets the entire cell's format to Bold. And if there's only 1
vbLF in the middle of things, then all after that character gets set to bold.

Hope that helps.

Thanks Dave.

I did my last posting at 02.05 AM, so I went to bed and saw your answer
just now. Once in bed I realized that I could have explained myself much
better by simply saying that I wanted to do something with the third line in
the cell, but I was focused too much at the cornerstones of the third line,
the chr(10)'s. These are needed of course to determine what's on that third
line. Stupid that I didn't think of an InStr in an InStr. I'm sorry, too
late at night I guess.

Question 1:
Is the number of possible "nested" InStr's unlimited?

Question 2:
You wrote "... if that length portion was greater than 0". Did you mean
wether the third line contains any characters - other than (the third)
chr(10)?

Jack.

Option Explicit
Sub testme()

Dim FirstPos As Long
Dim SecondPos As Long
Dim myCell As Range

Set myCell = ActiveSheet.Range("A1")

FirstPos = InStr(1, myCell.Value, vbLf, vbTextCompare)
SecondPos = InStr(FirstPos + 1, myCell.Value, vbLf, vbTextCompare)

myCell.Characters(FirstPos + 1, SecondPos - FirstPos - 1).Font.Bold =
True

End Sub

Excel's VBA is pretty forgiving. It didn't even care if that length
portion was
greater than 0.

Thank you Chip.

Problem is, I don't know on what posititions the second chr(10) and the
third chr(10) are. (with chr(10) I mean the character that causes the
content to go on at a new line in the cell) Their positions vary from
cell to cell in the column. The content is like
xxxx...xxxchr(10)xx...xxxxchr(10)xxxxxx...xxchr(10) xxxx etc. The x's
stand for other characters. The number of x's vary from cell to cell. In
this exaple I want to do things wits the xx...xxxx part because it is
between the second chr(10) and the third chr(10). What is the way to do
this with code?

Jack.


"Chip Pearson" <[email protected]> schreef in bericht

You can modify static text in a cell with code like

Range("A1").Characters(5, 3).Font.Bold = True

This turns characters 5, 6, and 7 bold.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



Hi all,

I want to do something (change font size and font color f.i.) with the
part
of the content of the active cell that is between the second and the
third
chr(10). What code do I need?

I thank you in advance for your advice.

Jack Sons
The Netherlands

.

 

[Dave Peterson]

In xl97+, you can have almost 32k worth of characters in a cell. So it's not
unlimited. But it's pretty large! <vbg>

This expression looks dubious to me:
myCell.Characters(21, -42).Font.Bold = ...

How can I say for negative 42 characters? But excel's VBA seems to think it's
ok and treats it as missing (the rest of the string will be affected).

See VBA's help for characters for info.

Thanks Dave.

I did my last posting at 02.05 AM, so I went to bed and saw your answer
just now. Once in bed I realized that I could have explained myself much
better by simply saying that I wanted to do something with the third line in
the cell, but I was focused too much at the cornerstones of the third line,
the chr(10)'s. These are needed of course to determine what's on that third
line. Stupid that I didn't think of an InStr in an InStr. I'm sorry, too
late at night I guess.

Question 1:
Is the number of possible "nested" InStr's unlimited?

Question 2:
You wrote "... if that length portion was greater than 0". Did you mean
wether the third line contains any characters - other than (the third)
chr(10)?

Jack.

Option Explicit
Sub testme()

Dim FirstPos As Long
Dim SecondPos As Long
Dim myCell As Range

Set myCell = ActiveSheet.Range("A1")

FirstPos = InStr(1, myCell.Value, vbLf, vbTextCompare)
SecondPos = InStr(FirstPos + 1, myCell.Value, vbLf, vbTextCompare)

myCell.Characters(FirstPos + 1, SecondPos - FirstPos - 1).Font.Bold =
True

End Sub

Excel's VBA is pretty forgiving. It didn't even care if that length
portion was
greater than 0.


Jack Sons wrote:

Thank you Chip.

Problem is, I don't know on what posititions the second chr(10) and the
third chr(10) are. (with chr(10) I mean the character that causes the
content to go on at a new line in the cell) Their positions vary from
cell to cell in the column. The content is like
xxxx...xxxchr(10)xx...xxxxchr(10)xxxxxx...xxchr(10) xxxx etc. The x's
stand for other characters. The number of x's vary from cell to cell. In
this exaple I want to do things wits the xx...xxxx part because it is
between the second chr(10) and the third chr(10). What is the way to do
this with code?

Jack.


"Chip Pearson" <[email protected]> schreef in bericht


You can modify static text in a cell with code like

Range("A1").Characters(5, 3).Font.Bold = True

This turns characters 5, 6, and 7 bold.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



Hi all,

I want to do something (change font size and font color f.i.) with the
part
of the content of the active cell that is between the second and the
third
chr(10). What code do I need?

I thank you in advance for your advice.

Jack Sons
The Netherlands

 

[Dave Peterson]

Ignore my response about the nesting. I misread your question. You had a
question about the nesting of instr()'s. Not the number of vblf's in a cell.

In xl97+, you can have almost 32k worth of characters in a cell. So
it's not unlimited. But it's pretty large! <vbg>

This expression looks dubious to me:
myCell.Characters(21, -42).Font.Bold = ...

How can I say for negative 42 characters? But excel's VBA seems to
think it's ok and treats it as missing (the rest of the string will be
affected).

See VBA's help for characters for info.

Thanks Dave.

I did my last posting at 02.05 AM, so I went to bed and saw your
answer just now. Once in bed I realized that I could have explained
myself much better by simply saying that I wanted to do something with
the third line in the cell, but I was focused too much at the
cornerstones of the third line, the chr(10)'s. These are needed of
course to determine what's on that third line. Stupid that I didn't
think of an InStr in an InStr. I'm sorry, too late at night I guess.

Question 1:
Is the number of possible "nested" InStr's unlimited?

Question 2:
You wrote "... if that length portion was greater than 0". Did you
mean wether the third line contains any characters - other than (the
third) chr(10)?

Jack.

Option Explicit
Sub testme()

Dim FirstPos As Long
Dim SecondPos As Long
Dim myCell As Range

Set myCell = ActiveSheet.Range("A1")

FirstPos = InStr(1, myCell.Value, vbLf, vbTextCompare)
SecondPos = InStr(FirstPos + 1, myCell.Value, vbLf, vbTextCompare)

myCell.Characters(FirstPos + 1, SecondPos - FirstPos -
1).Font.Bold = True

End Sub

Excel's VBA is pretty forgiving. It didn't even care if that length
portion was
greater than 0.


Jack Sons wrote:


Thank you Chip.

Problem is, I don't know on what posititions the second chr(10) and
the third chr(10) are. (with chr(10) I mean the character that
causes the content to go on at a new line in the cell) Their
positions vary from cell to cell in the column. The content is like
xxxx...xxxchr(10)xx...xxxxchr(10)xxxxxx...xxchr(10) xxxx etc. The
x's stand for other characters. The number of x's vary from cell to
cell. In this exaple I want to do things wits the xx...xxxx part
because it is between the second chr(10) and the third chr(10). What
is the way to do this with code?

Jack.


"Chip Pearson" <[email protected]> schreef in bericht


You can modify static text in a cell with code like

Range("A1").Characters(5, 3).Font.Bold = True

This turns characters 5, 6, and 7 bold.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



Hi all,

I want to do something (change font size and font color f.i.) with
the part
of the content of the active cell that is between the second and
the third
chr(10). What code do I need?

I thank you in advance for your advice.

Jack Sons
The Netherlands

 

[Jack Sons]

Thanks for your explanation.

Jack.

#1 - not sure what you mean about "nested" Instr() limits. But I think
that
the answer is no - you can nest them pretty deep in one formula, until the
formula either becomes so complex that the VB engine cannot evaluate it,
or
until you run out of stack space.
For example, you could do away with FirstPos and SecondPos entirely in
Dave's code and use this (hard to decipher later) line:
myCell.Characters(InStr(1, myCell.Value, vbLf, vbTextCompare) + 1, _
InStr(InStr(1, myCell.Value, vbLf, vbTextCompare) + 1, _
myCell.Value, vbLf, vbTextCompare) - _
InStr(1, myCell.Value, vbLf, vbTextCompare) - 1).Font.Bold = True
If that's what you mean by nesting InStr()s

For #2 - even if the cell is empty, it will not throw an error, in that
case
it basically sets the entire cell's format to Bold. And if there's only 1
vbLF in the middle of things, then all after that character gets set to
bold.

Hope that helps.

Thanks Dave.

I did my last posting at 02.05 AM, so I went to bed and saw your answer
just now. Once in bed I realized that I could have explained myself much
better by simply saying that I wanted to do something with the third line
in
the cell, but I was focused too much at the cornerstones of the third
line,
the chr(10)'s. These are needed of course to determine what's on that
third
line. Stupid that I didn't think of an InStr in an InStr. I'm sorry, too
late at night I guess.

Question 1:
Is the number of possible "nested" InStr's unlimited?

Question 2:
You wrote "... if that length portion was greater than 0". Did you mean
wether the third line contains any characters - other than (the third)
chr(10)?

Jack.

Option Explicit
Sub testme()

Dim FirstPos As Long
Dim SecondPos As Long
Dim myCell As Range

Set myCell = ActiveSheet.Range("A1")

FirstPos = InStr(1, myCell.Value, vbLf, vbTextCompare)
SecondPos = InStr(FirstPos + 1, myCell.Value, vbLf, vbTextCompare)

myCell.Characters(FirstPos + 1, SecondPos - FirstPos - 1).Font.Bold
=
True

End Sub

Excel's VBA is pretty forgiving. It didn't even care if that length
portion was
greater than 0.


Jack Sons wrote:

Thank you Chip.

Problem is, I don't know on what posititions the second chr(10) and
the
third chr(10) are. (with chr(10) I mean the character that causes the
content to go on at a new line in the cell) Their positions vary from
cell to cell in the column. The content is like
xxxx...xxxchr(10)xx...xxxxchr(10)xxxxxx...xxchr(10) xxxx etc. The x's
stand for other characters. The number of x's vary from cell to cell.
In
this exaple I want to do things wits the xx...xxxx part because it is
between the second chr(10) and the third chr(10). What is the way to
do
this with code?

Jack.


"Chip Pearson" <[email protected]> schreef in bericht

You can modify static text in a cell with code like

Range("A1").Characters(5, 3).Font.Bold = True

This turns characters 5, 6, and 7 bold.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



Hi all,

I want to do something (change font size and font color f.i.) with
the
part
of the content of the active cell that is between the second and the
third
chr(10). What code do I need?

I thank you in advance for your advice.

Jack Sons
The Netherlands

.

 

[Jack Sons]

Thanks again Dave.

Jack.

Ignore my response about the nesting. I misread your question. You had a
question about the nesting of instr()'s. Not the number of vblf's in a
cell.

In xl97+, you can have almost 32k worth of characters in a cell. So it's
not unlimited. But it's pretty large! <vbg>

This expression looks dubious to me:
myCell.Characters(21, -42).Font.Bold = ...

How can I say for negative 42 characters? But excel's VBA seems to think
it's ok and treats it as missing (the rest of the string will be
affected).

See VBA's help for characters for info.

Thanks Dave.

I did my last posting at 02.05 AM, so I went to bed and saw your answer
just now. Once in bed I realized that I could have explained myself much
better by simply saying that I wanted to do something with the third
line in the cell, but I was focused too much at the cornerstones of the
third line, the chr(10)'s. These are needed of course to determine
what's on that third line. Stupid that I didn't think of an InStr in an
InStr. I'm sorry, too late at night I guess.

Question 1:
Is the number of possible "nested" InStr's unlimited?

Question 2:
You wrote "... if that length portion was greater than 0". Did you mean
wether the third line contains any characters - other than (the third)
chr(10)?

Jack.


"Dave Peterson" <[email protected]> schreef in bericht

Option Explicit
Sub testme()

Dim FirstPos As Long
Dim SecondPos As Long
Dim myCell As Range

Set myCell = ActiveSheet.Range("A1")

FirstPos = InStr(1, myCell.Value, vbLf, vbTextCompare)
SecondPos = InStr(FirstPos + 1, myCell.Value, vbLf, vbTextCompare)

myCell.Characters(FirstPos + 1, SecondPos - FirstPos - 1).Font.Bold
= True

End Sub

Excel's VBA is pretty forgiving. It didn't even care if that length
portion was
greater than 0.


Jack Sons wrote:


Thank you Chip.

Problem is, I don't know on what posititions the second chr(10) and
the third chr(10) are. (with chr(10) I mean the character that causes
the content to go on at a new line in the cell) Their positions vary
from cell to cell in the column. The content is like
xxxx...xxxchr(10)xx...xxxxchr(10)xxxxxx...xxchr(10) xxxx etc. The x's
stand for other characters. The number of x's vary from cell to cell.
In this exaple I want to do things wits the xx...xxxx part because it
is between the second chr(10) and the third chr(10). What is the way
to do this with code?

Jack.


"Chip Pearson" <[email protected]> schreef in bericht


You can modify static text in a cell with code like

Range("A1").Characters(5, 3).Font.Bold = True

This turns characters 5, 6, and 7 bold.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



Hi all,

I want to do something (change font size and font color f.i.) with
the part
of the content of the active cell that is between the second and the
third
chr(10). What code do I need?

I thank you in advance for your advice.

Jack Sons
The Netherlands