Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function/Programming help with excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function/Programming help with excel
On Sun, 2 Apr 2006 20:18:59 -0500, 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 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function/Programming help with excel
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" wrote in message news:Excel_Newbie09.25nzfa_1144027200.847@excelfor um-nospam.com... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function/Programming help with excel
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. -- 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function/Programming help with excel
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" wrote in message news:Excel_Newbie09.25nzfa_1144027200.847@excelfor um-nospam.com... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function/Programming help with excel
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" wrote in message ... 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" wrote in message news:Excel_Newbie09.25nzfa_1144027200.847@excelfor um-nospam.com... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function/Programming help with excel
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function/Programming help with excel
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" wrote in message ... 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" wrote in message news:Excel_Newbie09.25nzfa_1144027200.847@excelfor um-nospam.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
Need suggestions for some uses of Ms Excel | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |