offset and font change (excel 2002)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

This is very unclear. I tried adding

 and 

tags, but that doesn’t improve the layout much. Can you attach a small demo workbook?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

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

Like HansV, 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?

Reply | Quote

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.

Reply | Quote

If you still want each of the subtotals to be shown in Bold Red, take the following code that you have commented out near the end of DoItAll:

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

uncomment it and move it to immediately after

ActiveWorkbook.PrecisionAsDisplayed = False

which is a few lines earlier.

To have your final subtotal show in Bold Green, add the following code to the very end of DoItAll

    Range("G3").Activate
    ActiveSheet.Outline.ShowLevels RowLevels:=8
    ActiveCell.End(xlDown).Activate
    With ActiveCell
        .Font.Bold = True
        .Font.ColorIndex = 10
    End With

In the second line of the code, I have set RowLevels to 8 – the maximum. In your specific example, it could be set at 3. Using 3, however, may be inflexible if your circumstances change.

Some of the simpler ways in which you could speed up your code could be by converting something like:

    Columns("H:M").Select
    Selection.delete Shift:=xlToLeft

into

    Columns("H:M").delete Shift:=xlToLeft

and

    Range("A3").Select
    Selection.autofilter
    Selection.autofilter Field:=9, Criteria1:="YES"

into

    Range("A3").autofilter Field:=9, Criteria1:="YES"

Also, in many instances you can replace “.Select” with “.Activate”. (Rather than doing this by hand, you could copy all the code in Word, carry out a Search and Replace and then copy it back into the Module. Make sure you back up your code first! )

HTH

Reply | Quote

my greatfull thanks to all who have contributed to answering my cry for help. your suggestion works a treat and the comments made by your self will be acted upon.
for any one who is interested the reason for the program is a fail safe devise. i work for a wholesaler who delivers orders when the value of the order reaches a given value. i try to stop those accounts who fall short by less than 10% from not getting there order delivered.

Reply | Quote

my greatfull thanks to all who have contributed to answering my cry for help. your suggestion works a treat and the comments made by your self will be acted upon.
for any one who is interested the reason for the program is a fail safe devise. i work for a wholesaler who delivers orders when the value of the order reaches a given value. i try to stop those accounts who fall short by less than 10% from not getting there order delivered.

Reply | Quote

If you still want each of the subtotals to be shown in Bold Red, take the following code that you have commented out near the end of DoItAll:

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

uncomment it and move it to immediately after

ActiveWorkbook.PrecisionAsDisplayed = False

which is a few lines earlier.

To have your final subtotal show in Bold Green, add the following code to the very end of DoItAll

    Range("G3").Activate
    ActiveSheet.Outline.ShowLevels RowLevels:=8
    ActiveCell.End(xlDown).Activate
    With ActiveCell
        .Font.Bold = True
        .Font.ColorIndex = 10
    End With

In the second line of the code, I have set RowLevels to 8 – the maximum. In your specific example, it could be set at 3. Using 3, however, may be inflexible if your circumstances change.

Some of the simpler ways in which you could speed up your code could be by converting something like:

    Columns("H:M").Select
    Selection.delete Shift:=xlToLeft

into

    Columns("H:M").delete Shift:=xlToLeft

and

    Range("A3").Select
    Selection.autofilter
    Selection.autofilter Field:=9, Criteria1:="YES"

into

    Range("A3").autofilter Field:=9, Criteria1:="YES"

Also, in many instances you can replace “.Select” with “.Activate”. (Rather than doing this by hand, you could copy all the code in Word, carry out a Search and Replace and then copy it back into the Module. Make sure you back up your code first! )

HTH

Reply | Quote

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.

Reply | Quote

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

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

Like HansV, 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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

This is very unclear. I tried adding

 and 

tags, but that doesn’t improve the layout much. Can you attach a small demo workbook?

Reply | Quote

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

Reply | Quote

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

Reply | Quote