I am using subtotals on a spreadsheet and then I want to hightlight everything in that columnd that is less then the subtotal below it. Then after the subtotal again highlight everything that is less then that subtotal. How can I do this? I have some clues but I don't know where to begin!!!
wouldn't that be everything?I want to hightlight everything in that columnd that is less than the subtotal
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
no because it is a Average of the percentage above it subtotal. I actually do the other subtotals as sum and then I use code to find the where to put the averages of the percentages and I do that formula then.
so the numbers above the subtotal are not the figures being subtotaled?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
no the subtotal is actually an average of the numbers above it.
Why not just use a Pivot Table?
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
so it's not a subtotal....
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
sorry,
the reason why I am saying subtotal is the rest of the line is subtotals and I forgot I did them another way and also so you understand that there are multiple lines of it after each section.
Assuming that the labels are in column A, and the amounts oin column B, use conditional formatting on A2:A20 (or adjust to the real range) with a formula of
=$B2<INDEX($B2:$B$20,MIN(IF(ISNUMBER(FIND("Average",$A2:$A$20)),ROW($A2:$A$ 20)))-MIN(ROW($B2:$B$20)+1))
Sorry, small faux-pas on the formula
=$B2<INDEX($B2:$B$20,MIN(IF(ISNUMBER(FIND("Average",$A2:$A$20)),ROW($A2:$A$ 20)-MIN(ROW($B2:$B$20))+1)))
Yes, I know how to do it in a formula lucas but I want to do it in code
I think poor Daniel is getting spread too thin....
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
You're right Bob!
He is running 5 different question at the moment.
Why? That is a class solution that I have given you, so why use code unnecessarily?Originally Posted by Djblois
Finally, a little humor in the mist of chaos
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
lol, thanks for the concern. I am trying to get my add-in as useful as possible for my users and make it as versatile as possible (not only for reports but for easier and quicker ways to do more advanced excel features for the layman)
Also, to the question of why would I do it in VBA, well because I am putting it in my add-in for a report that would be run weekly by my bosses secretary.
Then I would create a template report, with all the formatting including CF, and open that from the add-in, and populate as required.
The problem with that is the data prints out of our proprietary software right to an excel spreadsheet. Also, the subtotals will not always be in the same spot the might be higher and sometimes lower.
My solution works out where the subtotals are, it is not dependent.
The other thing looks like a sticky though, although you could still use a template and just copy the data (is this where you want to remove rows and columns as well?) values wholesale onto the template.
no that is a seperate function, just for user created tables. that is why I didn't put them in the same thread.
Posting Permissions
Reply With Quote
