Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    offset and font change (excel 2002)

    i have been trying without any success to change a font to bold in an cell which is 2 x cells to the left (using " offset(0,-2) ") of an ActiveCell
    the following will only change the font of the ActiveCell to bold

    with Activecell.Font
    .Bold = true
    end with

    can anyone help? I don't mind if the whole row is bold or the font changes culor as long as it will stand out

    alexanderd

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: offset and font change (excel 2002)

    ActiveCell.Offset(0,-2).Font.Bold = True

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: offset and font change (excel 2002)

    having tried your suggestion and fopund that in my case it did not work for me. to explain further, what i want to do is make the font for the "Alloc" column "BOLD" or a colour.
    row Accno DelCode Name Ordnum Gen OrdVal Alloc
    34 0045510 Total 157
    84 0070350 Total 190
    260 0035905 Total 200
    344 0165930 Total 149
    382 0194860 Total 167
    399 0230280 Total 177
    404 0232900 Total 189
    527 0100011 Total 163
    820 0021945 Total 153
    973 0165970 Total 189
    1002 0010450 Total 166
    1102 0065960 Total 199
    1152 0100033 Total 198
    1178 0123286 Total 164
    1320 0020805 Total 178
    1444 0112746 Total 169
    1517 0141830 Total 154
    1525 0162860 Total 150
    1594 0232858 Total 159
    the code which follows in the range G4:G1600 in this instance carries on until each line of the table has been checked how do i get it to only go as far as the last subtotal in the worksheet, which can have 200 lines or 10,000 lines

    Option Explicit

    Sub SubtotalFont()
    Dim cell As Range

    For Each cell In Range("G4:G1600")
    Cells.Find(What:="subtotal", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    Selection.Font.Bold = True
    Next
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: offset and font change (excel 2002)

    This is very unclear. I tried adding <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags, but that doesn't improve the layout much. Can you attach a small demo workbook?

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: offset and font change (excel 2002)

    it dose look like an idiots been at it see attached file

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: offset and font change (excel 2002)

    There is no subtotal in the table, so how should we test this?

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: offset and font change (excel 2002)

    only the subtotals are shown under the alloc column running the macro changes the font to bold
    if you prefer i can send an excel file

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: offset and font change (excel 2002)

    Yes, it would be helpful if you posted a workbook. A Word table is not much good if we are to test Excel code. You can trim it - we don't need 10,000 rows to test, leave just enough to give us an idea.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: offset and font change (excel 2002)

    attached is a zip file with my clumsy macro (which works for me in approx 100 seconds)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: offset and font change (excel 2002)

    What is the point of the workbook you attached? The word "subtotal" doesn't occur in any cell, so there is nothing to do for the macro, and nothing to test.

    Could you attach something useful? Please?

  11. #11
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: offset and font change (excel 2002)

    please run the first macro( it is quite safe ) takes approx 1 minute to run but is changes the results once i trty to save them

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: offset and font change (excel 2002)

    I get a series of errors. The code refers several times to columns that are empty or don't have column headers, so I get "Index out of range" or "AutoFilter method of Range class failed". It may have to do with the fact that I an using a non-English version of Excel. I can't do anything with this, sorry.

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: offset and font change (excel 2002)

    Having followed this thread, I have also looked at your attachment.

    >please run the first macro
    This seems to be DoItAll.

    Like <!profile=HansV>HansV<!/profile>, I received about 4 "AutoFilter method of Range class failed" error messages. I did not get any other error messages. Accordingly, I ran the code so that it stepped past these error messages.

    Having then run the SubtotalFont_Bold code, Worksheet com041 has all the Totals values (and only those values) in bright red.

    Other than the AutoFilter errors, its seems that the code is doing what you first intended. Is this the case, or is there something more to be explained?
    Gre

  14. #14
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: offset and font change (excel 2002)

    my apologies i tried to save to much ie less than 100k

  15. #15
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: offset and font change (excel 2002)

    i have resent the attachment and this time not cut out as much as last time (100K limit)

    as you have found out the subtotals which are hilighted in red uses the full range (G4:G1500) before it exits.
    i would like it to exit after it finds the last subtotal which could be only 2 or 100 subtotals and could have the last subtotal on row 1000 or on row 50
    your patience and help are much appreciated.

Page 1 of 2 12 LastLast

Posting Permissions

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