[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel VBA Format Subtotal row with color

Posted on 2013-01-30
13
Medium Priority
?
1,609 Views
Last Modified: 2013-01-31
I am trying to add color (and eventually a border) to my subtotal rows. I tried conditional formatting but it colors the entire row.  My data is only in colums A through AA.
I think there is something wrong with the way I select the range but I can't figure this out.
I have nested subtotals that total on columns C and A which might be causing problems as well.
Sub FormatSubtotalRow()

Dim LastRow As Long
Dim i As Long

Application.ScreenUpdating = False

 With Sheets("Accrual")
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
      For i = 2 To LastRow
      If Range("A" And i) = "Subtotal" Then
      Range("A" And i).Interior.ColorIndex = 36
      End If
      Next i
End With

Application.ScreenUpdating = True

End Sub

Open in new window

0
Comment
Question by:minamina6
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 38836540
well I have fixed a bit your sub that had several problems here it is

Sub FormatSubtotalRow()

Dim LastRow As Long
Dim i As Long

Application.ScreenUpdating = False

 With Sheets("Accrual")
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
      For i = 2 To LastRow
      If LCase(Range("A" & i)) = "subtotal" Then
      Range("A" & i).Interior.ColorIndex = 36
      End If
      Next i
End With

Application.ScreenUpdating = True

End Sub

Open in new window


Now this should only color Cell A that it encounter Subtotal

Now if you want to color the whole line (From Col A to AA) that has the Subtotal whcih I would guess you would want then change
This line
Range("A" & i).Interior.ColorIndex = 36

By this line
Range("A" & i & ":AA" & i).Interior.ColorIndex = 36

Let me know
gowflow
0
 

Author Comment

by:minamina6
ID: 38837023
I tried your code using the Range ("A" & i & ":A" & i)  but nothing actually changed colors.  I have attached my spreadsheet so you can get a better idea of what I'm working with.
AccrualMacro3.xls
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38837130
check it out now
gowflow
AccrualMacro3.xlsm
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:minamina6
ID: 38837165
Gowflow,

I need to ask a favor. Would you save your example in an earlier version of Excel in .xlm format? The client only has Excel 2000 software. I can't open .xlsm formatted files.
Thanks!
0
 
LVL 31

Accepted Solution

by:
gowflow earned 600 total points
ID: 38838248
here it s in 97-2003 version. You simply do save as and choose from the list macro enabled and in the type dropdown choose Earlier .xls 97-2003.

gowflow
AccrualMacro3.xls
0
 

Author Comment

by:minamina6
ID: 38839169
Thanks, I will review it today. The system here won't even let me open .xlsm files, let alone choose an earlier version.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38839346
the last file I posted is .xls aren't you able ot open it ? it is saved in 97-2003 version
gowflow
0
 

Author Comment

by:minamina6
ID: 38839350
Sorry I wasn't clear. The .xls version opened just fine. The code works exactly like I hoped it would. Thank you for your help.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38839774
ok fine. I just noticed you had allocated 150 points for that question is there a reason ?
gowflow
0
 

Author Comment

by:minamina6
ID: 38839791
I don't know the proper points to allocate.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38839826
Well let me help you. It is not that I am after points in the contrary I only noticed it now when the Answer was selected by you I noticed it comes out in big Green 150 with underneith the appreciation you give either A = Excellent, B = Good and C (honestly I don't know coz I never got a C)

So let me tell you how usually Experts react to questions, some of them obviously will not look at the points allocated for the question and will rightaway if they have an answer reply etc...

BUT, most of Experts do look for points and when you rate a question at 150 when the max is 500 you have much less chances to get Expert's attention to help you. So the more oyu want Experts to give your question fastest attention the more points you allocate.

Now as a general rule and what I can tell you from personal experience being Expert on this site since October 2009 the vast majority of questions are rated at full 500 very few are at less than that and surely it is very rare cases I have seen at 150 or less.

Now again this depends on the type of subscription you have when your premium member then the points are illimited to you and you can ask as many questions as you want but I guess there are plans which I am not too familiar with where the number of quesiton asked are limited as well as the points allocated are limited for that member.

I hope I have answered your question and pls don't worry about the points in this question as it is clear it was not intentional on your part but maybe for lack of knowledge.

Regards
gowflow
0
 

Author Comment

by:minamina6
ID: 38839885
Got it! I thought the points awarded were relevant to the difficulty of the question. If most people rate them at the max, I will do that from now on. Thanks for the additional assistance.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38840098
Your welcome and pls feel free to post here a link to a new question you would like assistance for.
gowflow
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question