Hey guys. I am new here and new to excel so go easy on me
I have a colum with 21 random characters in each cell. Is it possible to find the 11th character in each cell within only that colum and to make that character bold or red or something else so as to make it stand out. Not sure if this could be done in excel so I thought I might seek some expert help :D
Cheers
On Sun, 2 Apr 2006 20:18:59 -0500, Excel_Newbie09
<[email protected]> wrote:
>
>Hey guys. I am new here and new to excel so go easy on me
>
>I have a colum with 21 random characters in each cell. Is it possible
>to find the 11th character in each cell within only that colum and to
>make that character bold or red or something else so as to make it
>stand out. Not sure if this could be done in excel so I thought I might
>seek some expert help :D
>
>Cheers
It can be done if the contents of the cell is a text string. It cannot be done
if the string is constructed as the result of an equation.
You can go into each cell, select the eleventh character, and then select the
font characteristics of that character.
Or you can do it with a macro. To enter the macro, <alt><F11> opens the VB
Editor. Ensure your project is highlighted in the project explorer window,
then Insert/Module and paste the code below into the window that opens.
<alt-F8> will open the macro dialog box. Select Bold11 and <Run>.
Note that, as written, the macro assumes that your range to be process is
A1:A100. You can easily change this.
The macro also rewrites the range so that it is a Text string, and NOT an
equation. Any equation you had in there will be destroyed. So BACKUP your
worksheet before running this.
==========================
Option Explicit
Sub Bold11()
Dim Src As Range
Dim c As Range
Dim AC As Range
'set range with the 21 random characters
Set Src = [A1:A100]
'remember where the cursor was
Set AC = ActiveCell
'ensure range is only text strings; this may not be necessary
Src.Copy
Src.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
AC.Select
'Bold and redden the 11th charcter in each
For Each c In Src
c.Characters(11, 1).Font.Bold = True
c.Characters(11, 1).Font.Color = vbRed
Next c
End Sub
============================
--ron
Sub Bold11thCharacter()
Dim cell As Range
For Each cell In Selection
If Len(cell) > 10 Then
cell.Characters(11, 1).Font.Bold = True
End If
Next
End Sub
select the cells in the column and run the macro.
Assumes the characters are constants and not produced with a formula
--
Regards,
Tom Ogilvy
"Excel_Newbie09"
<[email protected]> wrote in
message news:[email protected]...
>
> Hey guys. I am new here and new to excel so go easy on me
>
> I have a colum with 21 random characters in each cell. Is it possible
> to find the 11th character in each cell within only that colum and to
> make that character bold or red or something else so as to make it
> stand out. Not sure if this could be done in excel so I thought I might
> seek some expert help :D
>
> Cheers
>
>
> --
> Excel_Newbie09
> ------------------------------------------------------------------------
> Excel_Newbie09's Profile:
http://www.excelforum.com/member.php...o&userid=33094
> View this thread: http://www.excelforum.com/showthread...hreadid=529050
>
Thanks for all the help guys. Got is working now with your codes. This macros thing looks to be really useful. Might have to read about it.
Sub HighlightEleventhCharacter()
'Click on a cell and run the macro to "highlight" the 11th character
With ActiveCell.Characters(Start:=11, Length:=1).Font
.FontStyle = "Bold"
.Size = 14
.ColorIndex = 3
End With
End Sub
Vaya con Dios,
Chuck, CABGx3
"Excel_Newbie09"
<[email protected]> wrote in
message news:[email protected]...
>
> Hey guys. I am new here and new to excel so go easy on me
>
> I have a colum with 21 random characters in each cell. Is it possible
> to find the 11th character in each cell within only that colum and to
> make that character bold or red or something else so as to make it
> stand out. Not sure if this could be done in excel so I thought I might
> seek some expert help :D
>
> Cheers
>
>
> --
> Excel_Newbie09
> ------------------------------------------------------------------------
> Excel_Newbie09's Profile:
http://www.excelforum.com/member.php...o&userid=33094
> View this thread: http://www.excelforum.com/showthread...hreadid=529050
>
Tom,
After running macro I don't see a visual change in the cells selected. If
on any given cell I press F2 (edit mode) then
only do I see the 11th character in bold, but after leaving edit mode it
doesn't show the change (that is reflected in the edit mode).
hummm,,
any suggestions?
Jim May
"Tom Ogilvy" <[email protected]> wrote in message
news:%23T7r%[email protected]...
> Sub Bold11thCharacter()
> Dim cell As Range
> For Each cell In Selection
> If Len(cell) > 10 Then
> cell.Characters(11, 1).Font.Bold = True
> End If
> Next
>
> End Sub
>
> select the cells in the column and run the macro.
>
> Assumes the characters are constants and not produced with a formula
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Excel_Newbie09"
> <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hey guys. I am new here and new to excel so go easy on me
>>
>> I have a colum with 21 random characters in each cell. Is it possible
>> to find the 11th character in each cell within only that colum and to
>> make that character bold or red or something else so as to make it
>> stand out. Not sure if this could be done in excel so I thought I might
>> seek some expert help :D
>>
>> Cheers
>>
>>
>> --
>> Excel_Newbie09
>> ------------------------------------------------------------------------
>> Excel_Newbie09's Profile:
> http://www.excelforum.com/member.php...o&userid=33094
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=529050
>>
>
>
This is kinda crude, but appears to do the job.........
Sub HighlightEleventhCharacter()
'Will highlight 11th character of each cell in column A
Dim lastrow As Long, r As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For r = lastrow To 1 Step -1
If Cells(r, "A") > 0 Then
Cells(r, "A").Select
With ActiveCell.Characters(Start:=11, Length:=1).Font
.FontStyle = "Bold" 'Sets the character to BOLD
.Size = 14 'Sets the character font to 14
.ColorIndex = 3 'Sets the character color
End With
End If
Next r
End Sub
Vaya con Dios,
Chuck, CABGx3
"Excel_Newbie09" wrote:
>
> Hey guys. I am new here and new to excel so go easy on me
>
> I have a colum with 21 random characters in each cell. Is it possible
> to find the 11th character in each cell within only that colum and to
> make that character bold or red or something else so as to make it
> stand out. Not sure if this could be done in excel so I thought I might
> seek some expert help :D
>
> Cheers
>
>
> --
> Excel_Newbie09
> ------------------------------------------------------------------------
> Excel_Newbie09's Profile: http://www.excelforum.com/member.php...o&userid=33094
> View this thread: http://www.excelforum.com/showthread...hreadid=529050
>
>
You don't have the 11th character visible in the column?
You are zoomed out and can't distinguish between bold and normal?
worked fine for me in xl97 and xl2003.
--
Regards,
Tom Ogilvy
"Jim May" wrote:
> Tom,
> After running macro I don't see a visual change in the cells selected. If
> on any given cell I press F2 (edit mode) then
> only do I see the 11th character in bold, but after leaving edit mode it
> doesn't show the change (that is reflected in the edit mode).
> hummm,,
> any suggestions?
> Jim May
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%23T7r%[email protected]...
> > Sub Bold11thCharacter()
> > Dim cell As Range
> > For Each cell In Selection
> > If Len(cell) > 10 Then
> > cell.Characters(11, 1).Font.Bold = True
> > End If
> > Next
> >
> > End Sub
> >
> > select the cells in the column and run the macro.
> >
> > Assumes the characters are constants and not produced with a formula
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Excel_Newbie09"
> > <[email protected]> wrote in
> > message news:[email protected]...
> >>
> >> Hey guys. I am new here and new to excel so go easy on me
> >>
> >> I have a colum with 21 random characters in each cell. Is it possible
> >> to find the 11th character in each cell within only that colum and to
> >> make that character bold or red or something else so as to make it
> >> stand out. Not sure if this could be done in excel so I thought I might
> >> seek some expert help :D
> >>
> >> Cheers
> >>
> >>
> >> --
> >> Excel_Newbie09
> >> ------------------------------------------------------------------------
> >> Excel_Newbie09's Profile:
> > http://www.excelforum.com/member.php...o&userid=33094
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=529050
> >>
> >
> >
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks