how to make each cell with subtotal in it to be bold with vba

rharri1972

Board Regular
Joined
Nov 12, 2021
Messages
132
Office Version
  1. 2019
Platform
  1. Windows
I have recorded a macro to subtotal my sheet of data.
I am using this as each change in Column E and adding subtotal to Column H.

this macro is the ending part in a larger script of code to complete a modified report.

is there anyway when using vba to make the subtotal BOLD so that it stands out?
 
I don't know what is in your column L but if the only cells with formulas are on the Sub Total rows then

Rich (BB code):
Columns("L").SpecialCells(xlFormulas).Interior.Color = vbYellow

Otherwise

VBA Code:
Intersect(Columns("H").SpecialCells(xlFormulas).EntireRow, Columns("L")).Interior.Color = vbYellow
Thank you so much Peter!! This works absolutely beautiful. This will complete my report and cut my time down to about 5 minutes to get the info i need instead of about an hour. I am very appreciative!!!
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hey Peter, I have been trying to put this together without bothering you again, However, for Column L which is the column that tells me how much to order. I only order anything in the Total line from the subtotal that is a negative number. So you just helped me with Highlighting the total line in this column...however I only want to highlight the negative numbers in subtotal row column L. This is absolutely my fault for not realizing before your help. Is this possible to do? I realized if I search to highlight negative numbers in this column(L4:L) then all negatives will be highlighted...not just the subtotal line.
 
Upvote 0
Try this snippet

VBA Code:
Dim c As Range

For Each c In Columns("L").SpecialCells(xlFormulas)
  If c.Value < 0 Then c.Interior.Color = vbYellow
Next c
 
Upvote 0
Try this snippet

VBA Code:
Dim c As Range

For Each c In Columns("L").SpecialCells(xlFormulas)
  If c.Value < 0 Then c.Interior.Color = vbYellow
Next c
Thank you again Peter!! How can I learn VBA like this? Just keep pounding it out ? School? i would love the knowledge!!
 
Upvote 0
Cheers. Glad to help.


I did most of my learning here in the forum, together with plenty of trial & error. No formal training. ?
Hey Peter. So all of the automation from the macros works GREAT from my laptop at home. I have tested several times no issues. I emailed it to my self and tried it for real today with my information and it gave me an error at the time it got into the subtotal. Again on my laptop at home it works... it was basically saying it can't tell where the headers are or what to subtotal or whatever. I have the range set from A3:M1800. Row 3 is the row where my headers are from A:M. Any reason it would "crash" like this?
 
Upvote 0
it gave me an error at the time it got into the subtotal.
What gave you an error exactly? That is, what was the exact error message and what line of code was highlighted when you clicked 'Debug'
 
Upvote 0
I will check this when i get to work and I will get that info to you
 
Upvote 0
I will check this when i get to work and I will get that info to you
OK, but I may be away from the forum for a week or more so it may be a while before I respond. However, somebody else might step in anyway. :)
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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