Highlighting part of a cell contents in Excel 2003

[Gregg]

I have values in cells A1:A10 expressed thusly: A1= 23_47
A2= 37_9
etc.. I would like to write code that will highlight individual
numbers in the cells A1:A10 that are greater than 32 and
less than 101. In cell A1 "47" would be highlighted, in cell
A2 "37" would be highlighted. Red can be the highlight color.
Gregg

 

[JLGWhiz]

The following would change the 3rd and 4th characters of
a string to red font. If the characters are number data type,
this code fails.

Sub clrfnt()
Sheets(1).Range("A1").Characters(3, 2) _
.Font.ColorIndex = 3
End Sub

 

[Gregg]

Thanks JLG, but I'm not sure how I could use your information in the example
I gave. I think a conditional for each loop of some sort might get the job
done
but I'm not sure.

 

[JLGWhiz]

Well, with a little imagination, I can visualize using an If Then statement
to set the conditions you mention and for every item that meets that
conditon, set the Characters function to select the part of the string you
want to highlight. The only thing is, that using this method, you would have
to have a consisten pattern like
Characters(4, 3) which would start at the fourth character from the left and
apply to three consecutive characters. But you cannot use it if the pattern
will vary in starting point or length to be applied.

 

[JLGWhiz]

Sorry, I took another look at your first posting and you are correct that the
characters function cannot be applied because you are trying to isolate part
of a string and evaluate it against a numerical value. That can be pretty
complex. Beyond my expertise.

 

[Rick Rothstein \(MVP - VB\)]

Assuming your numbers are always separated by underscore (underline)
characters, I think this macro will do what you want...

Sub ColorCertainNumbers()
Dim X As Long
Dim Start As Long
Dim Cell As Range
Dim Nums() As String
For Each Cell In Range("A1:A10")
Start = 1
Nums = Split(Cell.Value, "_")
For X = 0 To UBound(Nums)
If Not Nums(X) Like "*[!0-9]*" Then
If Nums(X) > 32 And Nums(X) < 101 Then
Cell.Characters(Start, Len(Nums(X))).Font.Color = vbRed
End If
End If
Start = Start + Len(Nums(X)) + 1
Next
Next
End Sub

By the way, the code, as written, will handle more than two numbers
(separated by underscores) per cell.

Rick

 

[Gregg]

First, thanks for your time Rick. I ran the macro on a sample column of
cells into which I had entered numbers which were separated by _
(underscore). The correct numbers' fonts were changed from black to red.
Can the background of the space the numbers occupy be red and the numbers
stay black?
A second problem is that though the macro worked right on the sample column
of cells with values only(numbers separated by underscores), when I ran the
macro on a column of numbers separated by underscores which had been
generated by a concatenation of values from two other columns the font color
of all the numbers in the cell were red if the first number in the cell was

32 and <101, but if the first number in the cell was outside these

parameters all the numbers in the cell stayed black even if there were
subsequent numbers in the cell which were >32 and >101. Sorry to be so long
in getting back to you. I'm in Vancouver, Canada.
--
Gregg

Assuming your numbers are always separated by underscore (underline)
characters, I think this macro will do what you want...

Sub ColorCertainNumbers()
Dim X As Long
Dim Start As Long
Dim Cell As Range
Dim Nums() As String
For Each Cell In Range("A1:A10")
Start = 1
Nums = Split(Cell.Value, "_")
For X = 0 To UBound(Nums)
If Not Nums(X) Like "*[!0-9]*" Then
If Nums(X) > 32 And Nums(X) < 101 Then
Cell.Characters(Start, Len(Nums(X))).Font.Color = vbRed
End If
End If
Start = Start + Len(Nums(X)) + 1
Next
Next
End Sub

By the way, the code, as written, will handle more than two numbers
(separated by underscores) per cell.

Rick

I have values in cells A1:A10 expressed thusly: A1= 23_47
A2=
37_9
etc.. I would like to write code that will highlight individual
numbers in the cells A1:A10 that are greater than 32 and
less than 101. In cell A1 "47" would be highlighted, in cell
A2 "37" would be highlighted. Red can be the highlight color.
Gregg

 

[Rick Rothstein \(MVP - VB\)]

Okay, I have fooled around with this for awhile and have observed the
following (remember, observed doesn't mean there is not a way around the
observation, only that I don't know of a way around it)...

It doesn't look like you can color the background of individual characters.
It looks like a font's background is transparent allowing the cell's color
to show through... I don't think you can partially color a cell (I believe
it is an all or nothing affair). Now, with that said, the Characters.Font
property of a cell (or range of cells) does have a Background property which
can be set to xlBackgroundAutomatic, xlBackgroundOpaque or
xlBackgroundTransparent. Note the xlBackgroundOpaque setting... I tried
using it but, within a cell's text, could not get it to make the character's
background different than the cell's color.

As to the concatenation of text... that seems to be problem. It seems that
to be able to highlight individual characters, those characters must
physically be in the cell. If the text gets there via a formula, it looks
like you can't do anything to the characters individually. Also, if the
entry in the cell is a number (no apostrophe in front of it making it text),
then it seem you also cannot highlight individual digits within that number.

Rick

First, thanks for your time Rick. I ran the macro on a sample column of
cells into which I had entered numbers which were separated by _
(underscore). The correct numbers' fonts were changed from black to red.
Can the background of the space the numbers occupy be red and the numbers
stay black?
A second problem is that though the macro worked right on the sample
column
of cells with values only(numbers separated by underscores), when I ran
the
macro on a column of numbers separated by underscores which had been
generated by a concatenation of values from two other columns the font
color
of all the numbers in the cell were red if the first number in the cell
was

32 and <101, but if the first number in the cell was outside these

parameters all the numbers in the cell stayed black even if there were
subsequent numbers in the cell which were >32 and >101. Sorry to be so
long
in getting back to you. I'm in Vancouver, Canada.
--
Gregg

Assuming your numbers are always separated by underscore (underline)
characters, I think this macro will do what you want...

Sub ColorCertainNumbers()
Dim X As Long
Dim Start As Long
Dim Cell As Range
Dim Nums() As String
For Each Cell In Range("A1:A10")
Start = 1
Nums = Split(Cell.Value, "_")
For X = 0 To UBound(Nums)
If Not Nums(X) Like "*[!0-9]*" Then
If Nums(X) > 32 And Nums(X) < 101 Then
Cell.Characters(Start, Len(Nums(X))).Font.Color = vbRed
End If
End If
Start = Start + Len(Nums(X)) + 1
Next
Next
End Sub

By the way, the code, as written, will handle more than two numbers
(separated by underscores) per cell.

Rick

I have values in cells A1:A10 expressed thusly: A1= 23_47
A2=
37_9
etc.. I would like to write code that will highlight individual
numbers in the cells A1:A10 that are greater than 32 and
less than 101. In cell A1 "47" would be highlighted, in cell
A2 "37" would be highlighted. Red can be the highlight color.
Gregg

 

[Gregg]

Thanks Rick, the second question I asked, about when the numbers are
generated by a formula the code not working properly is not a big deal. I
can always copy the numbers to the clipboard and repaste them in another
column which will paste the numbers without bringing the formulas along. I'm
sure there are other solutions to this problem too.
The highlighting problem is tougher. I'm sure you can highlight individual
letters or words in the Word Application, and I'm pretty sure it could be
coded. I am going to try to do something with the select method and see if
there is some way to change the color that the select method uses, because
the select method is basically a highlighting of whatever it is that you
select.
To finish, your original code is for all intents just what I was looking for
because now I have something to work with, whereas without the code I would
still be where I started: nowhere.
--
Gregg

Okay, I have fooled around with this for awhile and have observed the
following (remember, observed doesn't mean there is not a way around the
observation, only that I don't know of a way around it)...

It doesn't look like you can color the background of individual characters.
It looks like a font's background is transparent allowing the cell's color
to show through... I don't think you can partially color a cell (I believe
it is an all or nothing affair). Now, with that said, the Characters.Font
property of a cell (or range of cells) does have a Background property which
can be set to xlBackgroundAutomatic, xlBackgroundOpaque or
xlBackgroundTransparent. Note the xlBackgroundOpaque setting... I tried
using it but, within a cell's text, could not get it to make the character's
background different than the cell's color.

As to the concatenation of text... that seems to be problem. It seems that
to be able to highlight individual characters, those characters must
physically be in the cell. If the text gets there via a formula, it looks
like you can't do anything to the characters individually. Also, if the
entry in the cell is a number (no apostrophe in front of it making it text),
then it seem you also cannot highlight individual digits within that number.

Rick

First, thanks for your time Rick. I ran the macro on a sample column of
cells into which I had entered numbers which were separated by _
(underscore). The correct numbers' fonts were changed from black to red.
Can the background of the space the numbers occupy be red and the numbers
stay black?
A second problem is that though the macro worked right on the sample
column
of cells with values only(numbers separated by underscores), when I ran
the
macro on a column of numbers separated by underscores which had been
generated by a concatenation of values from two other columns the font
color
of all the numbers in the cell were red if the first number in the cell
was

32 and <101, but if the first number in the cell was outside these

parameters all the numbers in the cell stayed black even if there were
subsequent numbers in the cell which were >32 and >101. Sorry to be so
long
in getting back to you. I'm in Vancouver, Canada.
--
Gregg

Assuming your numbers are always separated by underscore (underline)
characters, I think this macro will do what you want...

Sub ColorCertainNumbers()
Dim X As Long
Dim Start As Long
Dim Cell As Range
Dim Nums() As String
For Each Cell In Range("A1:A10")
Start = 1
Nums = Split(Cell.Value, "_")
For X = 0 To UBound(Nums)
If Not Nums(X) Like "*[!0-9]*" Then
If Nums(X) > 32 And Nums(X) < 101 Then
Cell.Characters(Start, Len(Nums(X))).Font.Color = vbRed
End If
End If
Start = Start + Len(Nums(X)) + 1
Next
Next
End Sub

By the way, the code, as written, will handle more than two numbers
(separated by underscores) per cell.

Rick


I have values in cells A1:A10 expressed thusly: A1= 23_47
A2=
37_9
etc.. I would like to write code that will highlight individual
numbers in the cells A1:A10 that are greater than 32 and
less than 101. In cell A1 "47" would be highlighted, in cell
A2 "37" would be highlighted. Red can be the highlight color.
Gregg