Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help (Excel 2000)

    Hi All ... I have attached a file with a macro. This macro attempts to Bold the first two lines of B9 and B10, and italicsize the 3rd line.
    It uses the lengths that are in columns K,L, and M.

    Using a With statement, I can make it go through both cells B9 and B10, but I don't know how to make it use the correct lengths in K, L, and M.

    Any help would be appreciated.

    Thanks.
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help (Excel 2000)

    You need to do each cell spearately if you don't want to do exactly the same thing to each cell, like this:

    <pre>Sub Bold_Italic()

    Application.ScreenUpdating = False

    LenOfBold = Range("K9").Value + Range("L9").Value
    LenOfItalics = Range("M9").Value
    Range("B9").Characters(Start:=1, Length:=LenOfBold).Font.FontStyle = "Bold"
    Range("B9").Characters(Start:=LenOfBold + 1, Length:=LenOfItalics).Font.FontStyle = "Italic"
    LenOfBold = Range("K10").Value + Range("L10").Value
    LenOfItalics = Range("M10").Value
    Range("B10").Characters(Start:=1, Length:=LenOfBold).Font.FontStyle = "Bold"
    Range("B10").Characters(Start:=LenOfBold + 1, Length:=LenOfItalics).Font.FontStyle = "Italic"

    Range("A1").Select
    Application.ScreenUpdating = False
    End Sub
    </pre>


    If you need to do a larger number of cells, then you will need a loop that goes through the cells.

    In your character counts, you also need to account for the new line character that is at the end of each line in the cell.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help (Excel 2000)

    Hi Legare. I am sorry. I did not give all the information. I have a value in cell AA1 that will dictate how many cells in column B (starting at B9) that need to be formatted in this fashion.
    It is the looping that I do not understand. I will need to loop through cells B9 until B??, depending on the value in cell AA1, and keep track of the lengths stored in the corresponding columns K, L, and M.

    Any more help would be greatly appreciated.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help (Excel 2000)

    Try something like this:

    <pre>Sub Bold_Italic()
    Dim I As Long, LenOfBold As Long, LenOfItalics As Long
    Application.ScreenUpdating = False
    For I = 0 To Range("AA1").Value - 1
    LenOfBold = Range("K9").Offset(I, 0).Value + Range("L9").Offset(I, 0).Value
    LenOfItalics = Range("M9").Offset(I, 0).Value
    Range("B9").Offset(I, 0).Characters(Start:=1, Length:=LenOfBold).Font.FontStyle = "Bold"
    Range("B9").Offset(I, 0).Characters(Start:=LenOfBold + 1, Length:=LenOfItalics).Font.FontStyle = "Italic"
    Next I
    Range("A1").Select
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  5. #5
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help (Excel 2000)

    Thanks, Legare. I had written "Brute Force" code, but it covered more area than I needed and was time-consuming. I do appreciate your help.

Posting Permissions

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