Page 1 of 2 12 LastLast
Results 1 to 10 of 11

VBA format a range of cells

This is a discussion on VBA format a range of cells within the Excel Questions forums, part of the Question Forums category; Excel 97 Win98 How can I format the entire range named “Cal”? The range is now is B3:H75 I want ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    2,378

    Default


    Excel 97 Win98

    How can I format the entire range named “Cal”?
    The range is now is B3:H75

    I want the first line the default format (bold and font.size=13) and the second line
    (regular and font size=12)

    The following will format one cell

    Sub Macro1()

    With ActiveCell.Characters(Start:=1, Length:=12).Font
    .FontStyle = "Bold"
    .Size = 13
    End With
    With ActiveCell.Characters(Start:=13, Length:=18).Font
    .FontStyle = "Regular"
    .Size = 12
    End With
    End Sub



    Some cells have just 12 characters of text and the default is OK.

    Other cells contain more than 12. The first 12 s/b unchanged and other say from 13
    with Regular and font=12.

    [ This Message was edited by: Dave Patton on 2002-11-17 20:37 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,949

    Default

    I guess you have to do a loop through each cell

    For each Cll in Range("Call")
    'Your code to change here, but
    'change ActiveCell with Cll
    Next Cll

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    Sub CheckCells(CurrRange As Range)
    For Each cell In CurrRange
    If cell.Value < 0 Then cell.Font.Bold = True Else cell.Font.Bold = False
    Next cell
    End Sub


    Sub BoldNegative()
    If TypeName(Selection) <> "Range" Then Exit Sub
    ' If one cell is selected, check it and exit
    If Selection.Count = 1 Then
    CheckCells (Selection)
    Exit Sub
    End If
    On Error Resume Next
    ' Check the cells with constants
    Call CheckCells(Selection.SpecialCells(xlConstants, 23))
    ' Check the cells with formulas
    Call CheckCells(Selection.SpecialCells(xlFormulas, 23))
    End Sub


    Note: Selection can be the actual current selection as a range or a named range! Hope these help. JSW
    JSW: Try and try again: "The way of the Coder!"

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    2,378

    Default


    Thanks Juan Pablo G. and Joe Was.

    I used the For ...next
    and it worked.

    It was not fast but it worked.

    Thanks again


    I had difficulty connecting to the site.
    Dave

  5. #5

    Join Date
    Nov 2002
    Posts
    981

    Default

    I don't think you need to loop through each cell.

    Just try changing ActiveCell to Range("Cal") :-

    With Range("Cal").Characters(Start:=1, Length:=12).Font
    .FontStyle = "Bold"
    .Size = 13
    End With
    With Range("Cal").Characters(Start:=13, Length:=18).Font
    .FontStyle = "Regular"
    .Size = 12
    End With

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,949

    Default

    On 2002-11-18 06:40, Ponsonby wrote:
    ...I don't think you need to loop through each cell...
    Yes, this is correct. Sorry, I should really test before posting...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    2,378

    Default

    On 2002-11-18 06:40, Ponsonby wrote:
    I don't think you need to loop through each cell.

    Just try changing ActiveCell to Range("Cal") :-

    With Range("Cal").Characters(Start:=1, Length:=12).Font
    .FontStyle = "Bold"
    .Size = 13
    End With
    With Range("Cal").Characters(Start:=13, Length:=18).Font
    .FontStyle = "Regular"
    .Size = 12
    End With

    Thanks for the help.

    I inititally used this type of Sub but it did not work. The change shows for a very short while and then the information reverts to how it was.

    It may be because the cells contain text of diffferent lengths. All cells include the date as text dd-mmm-yyyy; some cells include additional text example "18-Nov-2002 Exec Mtg".

  8. #8

    Join Date
    Nov 2002
    Posts
    981

    Default

    That's strange. I've just tested it and it works for me.

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,949

    Default

    Yes, I tested with different strings of different lengths, and each one changed the first 13 characters. I tested that on 2000.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  10. #10

    Join Date
    Nov 2002
    Posts
    981

    Default

    Have tested again and there is a problem, but don't know why.

    Everyting works if all cells are longer than 12 characters.

    If some cells are only 12 characters, then it works up to the last line of code (.Size=12) but when that line is run, the charcters after character 12 revert to Bold instead of Regular.

    One way around this(I think) is to change the sequence of the code :-

    With Range("Cal").Font
    .FontStyle = "Regular"
    .Size = 12
    End With
    With Range("Cal").Characters(Start:=1, Length:=12).Font
    .FontStyle = "Bold"
    .Size = 13
    End With




    [ This Message was edited by: Ponsonby on 2002-11-18 16:24 ]

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com