Help (Excel 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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

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

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.

Reply | Quote

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.

Reply | Quote

Try something like this:

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

Reply | Quote

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.

Reply | Quote