Function/Programming help with excel

E

Excel_Newbie09

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
T

Tom Ogilvy

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"
 
E

Excel_Newbie09

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.
 
C

CLR

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"
 
J

Jim May

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
 
C

CLR

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
 
T

Tom Ogilvy

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.
 
Top