Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: Macro to modify contents of a cell

    Hi All,

    I'm trying to bold the first letter of a lot of cells. Of course, when I write a macro to do this, it changes all the cell text to exactly what was in the original cell I wrote the macro on. So now all my fields say the same thing.

    Any ideas would be appreciated.

    Thanks,

    Michele

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Michelle,

    Heres the code i came up with
    i initally recorded the process then tided up thecode that excel produced

    Code:
    Sub BoldFirst()
    
    'used to bold the first Character of the cell
    Dim rngCell As Range
        For Each rngCell In Selection
            rngCell.Characters(Start:=1, Length:=1).Font.Bold = True
            rngCell.Characters(Start:=2, Length:=Len(ActiveCell.Formula)).Font.Bold = False
        Next rngCell
        
    End Sub
    HTH
    Dave

  3. #3
    Join Date
    Nov 2004
    Posts
    4
    Works great! I always wondered if there was a way to do that.

    And I can guess that the ActiveCell.Formula will work for anything where I want to keep the cell contents and edit them, like using F2 normally.

    Thanks a lot,

    Michele

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by MJones58
    And I can guess that the ActiveCell.Formula will work for anything where I want to keep the cell contents and edit them, like using F2 normally.
    Sort of... ActiveCell refers to the Actual selected worksheet cell. You also have ActiveSheet, ActiveWorkbook...

    In David's Example he used a loop that defined each cell in the selected Range to the 'rngCell' variable. This changed the reference of rngCel with each itteration of the loop.

    This line:
    rngCell.Characters(Start:=2, Length:=Len(ActiveCell.Formula)).Font.Bold = False

    "ActiveCell.Formula" would not change, because the selection was not changed during the loop process. So if you had All bold entries it may not unbold all the caracters 2 to x if the text Length is less than whatever cell happend to be active.

    See what I mean David? I think you would want to change this to:
    rngCell.Characters(Start:=2, Length:=Len(rngCell.Formula)).Font.Bold = False



    /
    ~

    Bill

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Bill

    Sorry i just missed that one i orginally worked on an individual cell hence the Activecell and then later put it into a loop,
    i produced this macro in all of about 5 minutes and didn't check it enough

    I know all about the use of activecell, it was just a mistake on my part

    Michelle if you use Bills line at the bottom this is what i was wanting to produce,


    Cheers
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •