Update ranges on worksheet failing - refers to wrong sheet

mrxwhite

New Member
Joined
Jul 1, 2004
Messages
25
Hi

Am trying to make all subtotal lines bold (ie the numbers themselves). Problem is that VBA is on Sheet1 and formatted data is on Sheet2. How do I make the ranges in the code below refer to the second sheet (without the ws references, the code reads data form the first sheet. With them I get a 400 error)?

TIA


Code:
'   Make the subtotal lines bold on the ReportData worksheet
    Set ws = Sheets("ReportData")
    
          For Each c In ws.Range([A1], [A65536].End(xlUp))
            If Right(c, 5) = "Total" Then
                ws.Range(c, c.End(xlToRight)).Font.Bold = True
            End If
        Next c
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I think the problem is with [A1] and [A65536].

Since you don't refer to the worksheet here VBA will refer to the currently active sheet.
Code:
Set ws = Sheets("ReportData")
LastRow = ws.Range("A65536").End(xlUp).Row

For Each c In ws.Range("A1:A" & LastRow)
    If Right(c, 5) = "Total" Then
        ws.Range(c, c.End(xlToRight)).Font.Bold = True
    End If
Next c
 
Upvote 0
Try:

Code:
'   Make the subtotal lines bold on the ReportData worksheet 
    Set ws = Sheets("ReportData") 
    Set Rng = ws.Range("A1:A" & ws.Range("A65536").End(xlUp).Row) 
    For Each c In Rng 
        If Right(c, 5) = "Total" Then 
            ws.Range(c, c.End(xlToRight)).Font.Bold = True 
        End If 
    Next c
 
Upvote 0
Thanks - all great ideas (I used Andrew's). One last thing, when I run the code it only bolds the first cell to the subtotal to the right in the row but I have 12. What changes do I need to make so that it hits all cells that contain subtotals (F:Q)?

Thanks for the lightning-fast responses!
 
Upvote 0
This is so easy using Conditional Formatting, select column F:Q, do Format/Conditional Formatting, Formula Is with formula of =RIGHT($A1,5)="Total" and a format of bold, and it's done.
 
Upvote 0
Yes - and C, D and E. There can be subtotals in F to Q (but some of these can also be blank. Example line below

Code:
Total	<empty>	<empty>	<empty>	<empty>	8.74	11.03	12.26	17.17	17.93	14.93	12.53	11.87	10.12	10.28	7.58	15.70
 
Upvote 0

Forum statistics

Threads
1,223,431
Messages
6,172,079
Members
452,444
Latest member
ShaImran193

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top