Update ranges on worksheet failing - refers to wrong sheet

M

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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

D
  • Solved
How to include date range in my PDF export name
Replies
4
Views
92
druck21
D
N
  • Question Question
FormulaR1C1 wrong?
Replies
9
Views
164
RoryA
RoryA
T
  • Solved
CSV import overwriting the next column after the last one in the CSV file
Replies
7
Views
329
Joe4
Joe4
itsgrady
  • Question Question
VBA - Cell references instead of hard coding
Replies
1
Views
304
Cubist
Cubist
C
  • Question Question
Using >= in the Find method to find all values greater than $200 in column E. (i.e., ">=$200" - which is wrong)
Replies
8
Views
595
chazrab
C

Forum statistics

Threads
1,224,862
Messages
6,181,466
Members
453,045
Latest member
Abraxas_X

Share this page

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