formatting cells
I need to format a text cell so the first xx chars in the cell are bold and
the rest are not. ie: This is the cell text ( and the word "This" is bolded but the rest of the text is regular text) I can do it by hand but how do you do this with a macro? Thanks, Eric |
Hi Eric,
If you recorded a macro you would have a very good start. See http://www.mvps.org/dmcritchie/excel/proper.htm#surname If not familiar with installing and using a macro see http://www.mvps.org/dmcritchie/excel/getstarted.htm and once you've installed and used a macro and have a new macro question it would be better to post in excel.programming -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Eric" wrote in message ... I need to format a text cell so the first xx chars in the cell are bold and the rest are not. ie: This is the cell text ( and the word "This" is bolded but the rest of the text is regular text) I can do it by hand but how do you do this with a macro? Thanks, Eric |
Lookup absolute and relative cell references in the Help files.
Also check out these links: http://www.cpearson.com/excel/relative.htm http://support.microsoft.com/default...b;en-us;820204 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Eric" wrote in message ... I need to format a text cell so the first xx chars in the cell are bold and the rest are not. ie: This is the cell text ( and the word "This" is bolded but the rest of the text is regular text) I can do it by hand but how do you do this with a macro? Thanks, Eric |
I HATE when I do this ... post to the wrong thread ! ! !
-- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "RagDyer" wrote in message ... Lookup absolute and relative cell references in the Help files. Also check out these links: http://www.cpearson.com/excel/relative.htm http://support.microsoft.com/default...b;en-us;820204 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Eric" wrote in message ... I need to format a text cell so the first xx chars in the cell are bold and the rest are not. ie: This is the cell text ( and the word "This" is bolded but the rest of the text is regular text) I can do it by hand but how do you do this with a macro? Thanks, Eric |
RagDyer wrote:
Lookup absolute and relative cell references in the Help files. Also check out these links: http://www.cpearson.com/excel/relative.htm http://support.microsoft.com/default...b;en-us;820204 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Eric" wrote in message ... I need to format a text cell so the first xx chars in the cell are bold and the rest are not. ie: This is the cell text ( and the word "This" is bolded but the rest of the text is regular text) I can do it by hand but how do you do this with a macro? Thanks, Eric What has that got to do with my question? Eric |
RagDyer wrote:
I HATE when I do this ... post to the wrong thread ! ! ! -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "RagDyer" wrote in message ... Lookup absolute and relative cell references in the Help files. Also check out these links: http://www.cpearson.com/excel/relative.htm http://support.microsoft.com/default...b;en-us;820204 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Eric" wrote in message ... I need to format a text cell so the first xx chars in the cell are bold and the rest are not. ie: This is the cell text ( and the word "This" is bolded but the rest of the text is regular text) I can do it by hand but how do you do this with a macro? Thanks, Eric Ah, ok, then never mind my reply, i should read everything first Eric |
David McRitchie wrote:
Hi Eric, If you recorded a macro you would have a very good start. See http://www.mvps.org/dmcritchie/excel/proper.htm#surname If not familiar with installing and using a macro see http://www.mvps.org/dmcritchie/excel/getstarted.htm and once you've installed and used a macro and have a new macro question it would be better to post in excel.programming I tried that but it fails to record when yo do this. Try it, it will error out of the record mode if you exit the cell after bolding part of the cell text Eric |
Hi Eric,
Actually I did record a macro before I posted, so what do you mean by it failed. Did you remember to do your bolding on the formula bar rather than in the cell. I gave you a link on a page easier to remember which was http://www.mvps.org/dmcritchie/excel/proper.htm#surname which points to a complete example on Surnames, Special formatting for LASTNAME, firstname http://www.mvps.org/dmcritchie/excel/surname.htm which calculates the xx length that the beginning of each cell in a range or selection is to be bolded. If you look at the macro the variable I actually use is i to find the comma which will determine the length to be bolded (and capitalized). Separate macros were provided the "inner" macro to do the work. A macro that you would be able to see using Alt+F8 that invokes the inner macro, and finally a Change Event macro to invoke the "inner" macro so that an entry would be changed as soon as you entered a value into a cell. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Eric" wrote in message ... David McRitchie wrote: Hi Eric, If you recorded a macro you would have a very good start. See http://www.mvps.org/dmcritchie/excel/proper.htm#surname If not familiar with installing and using a macro see http://www.mvps.org/dmcritchie/excel/getstarted.htm and once you've installed and used a macro and have a new macro question it would be better to post in excel.programming I tried that but it fails to record when you do this. Try it, it will error out of the record mode if you exit the cell after bolding part of the cell text Eric |
David McRitchie wrote:
Hi Eric, Actually I did record a macro before I posted, so what do you mean by it failed. Did you remember to do your bolding on the formula bar rather than in the cell. I gave you a link on a page easier to remember which was http://www.mvps.org/dmcritchie/excel/proper.htm#surname which points to a complete example on Surnames, Special formatting for LASTNAME, firstname http://www.mvps.org/dmcritchie/excel/surname.htm which calculates the xx length that the beginning of each cell in a range or selection is to be bolded. If you look at the macro the variable I actually use is i to find the comma which will determine the length to be bolded (and capitalized). Separate macros were provided the "inner" macro to do the work. A macro that you would be able to see using Alt+F8 that invokes the inner macro, and finally a Change Event macro to invoke the "inner" macro so that an entry would be changed as soon as you entered a value into a cell. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Eric" wrote in message ... David McRitchie wrote: Hi Eric, If you recorded a macro you would have a very good start. See http://www.mvps.org/dmcritchie/excel/proper.htm#surname If not familiar with installing and using a macro see http://www.mvps.org/dmcritchie/excel/getstarted.htm and once you've installed and used a macro and have a new macro question it would be better to post in excel.programming I tried that but it fails to record when you do this. Try it, it will error out of the record mode if you exit the cell after bolding part of the cell text Eric Ok, here's snippet from your page, let me ask you some things: cell.Formula = UCase(cell.Formula) With cell.Font .FontStyle = "Bold" End With With cell.Characters(Start:=i + 1).Font .FontStyle = "Regular" End With why: With cell.Font .FontStyle = "Bold" End With is Cells(R, C).Font.Bold = True the same thing? Whats "With"? i dont get it, doesnt make sense to me. is With cell.Characters(Start:=i + 1).Font .FontStyle = "Regular" End With the same as Cells(R,C).Characters(<whats this parameter here?).Font.FontStyle="Bold" or Cells(R,C).Characters(0,5).Font.FontStyle="Bold" ' bold first 5 chars of cell? Thanks, Eric |
The real question is does the macro work for you or not, and
can you modify it to do what you want. I think it certainly meets what you asked for in your question -- you don't need the parts about capitalization. The WITH is used as a prefix to statements within it's scope that begin with a period -- they will be prefixed with the WITH part. They started from recorded macros with a lot of material being removed, but if there were several statements within the WITH it is supposed to be faster, it can make lines fit better without wrapping as well in other cases. With cell.Font .FontStyle = "Bold" End With is equivalent to cell.Font.FontStyle = "Bold" Look in your VBE Help and look for "characters property" expression.Characters(Start, Length) you can code the parameters or you can use Start= Length= so that you don't have to know the order that the parameters are in. also look up "range property" in your VBE help, specifically the topic: Range Property (Application, Range, or Worksheet Object) There is no need to use cells(Rptr, Cptr) relative to the entire worksheet, when you can work with a range and within that range work one cell at a time -- that one cell is a range that was named "cell" in the loop. The range of all of the cells to be looked at need not even be contiguous. If you look at the entire page that you were first referred to, you would see that some ranges are comprised on only the intersection of a selection and the text cells -- meaning each cell looked at must be in the used range or other selection and must be a text cell. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Eric" Ok, here's snippet from your page, let me ask you some things: cell.Formula = UCase(cell.Formula) With cell.Font .FontStyle = "Bold" End With With cell.Characters(Start:=i + 1).Font .FontStyle = "Regular" End With why: With cell.Font .FontStyle = "Bold" End With is Cells(R, C).Font.Bold = True the same thing? Whats "With"? i dont get it, doesnt make sense to me. is With cell.Characters(Start:=i + 1).Font .FontStyle = "Regular" End With the same as Cells(R,C).Characters(<whats this parameter here?).Font.FontStyle="Bold" or Cells(R,C).Characters(0,5).Font.FontStyle="Bold" ' bold first 5 chars of cell? Thanks, Eric |
David McRitchie wrote:
The real question is does the macro work for you or not, and can you modify it to do what you want. I think it certainly meets what you asked for in your question -- you don't need the parts about capitalization. The WITH is used as a prefix to statements within it's scope that begin with a period -- they will be prefixed with the WITH part. They started from recorded macros with a lot of material being removed, but if there were several statements within the WITH it is supposed to be faster, it can make lines fit better without wrapping as well in other cases. With cell.Font .FontStyle = "Bold" End With is equivalent to cell.Font.FontStyle = "Bold" Look in your VBE Help and look for "characters property" expression.Characters(Start, Length) you can code the parameters or you can use Start= Length= so that you don't have to know the order that the parameters are in. also look up "range property" in your VBE help, specifically the topic: Range Property (Application, Range, or Worksheet Object) There is no need to use cells(Rptr, Cptr) relative to the entire worksheet, when you can work with a range and within that range work one cell at a time -- that one cell is a range that was named "cell" in the loop. The range of all of the cells to be looked at need not even be contiguous. If you look at the entire page that you were first referred to, you would see that some ranges are comprised on only the intersection of a selection and the text cells -- meaning each cell looked at must be in the used range or other selection and must be a text cell. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Eric" Ok, here's snippet from your page, let me ask you some things: cell.Formula = UCase(cell.Formula) With cell.Font .FontStyle = "Bold" End With With cell.Characters(Start:=i + 1).Font .FontStyle = "Regular" End With why: With cell.Font .FontStyle = "Bold" End With is Cells(R, C).Font.Bold = True the same thing? Whats "With"? i dont get it, doesnt make sense to me. is With cell.Characters(Start:=i + 1).Font .FontStyle = "Regular" End With the same as Cells(R,C).Characters(<whats this parameter here?).Font.FontStyle="Bold" or Cells(R,C).Characters(0,5).Font.FontStyle="Bold" ' bold first 5 chars of cell? Thanks, Eric ok, thanks Eric |
You're welcome.
From: "Eric" ok, thanks |
All times are GMT +1. The time now is 04:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com