PDA

View Full Version : Highlight if less then subtotal



Djblois
03-26-2007, 09:06 AM
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!!!

lucas
03-26-2007, 09:14 AM
I want to hightlight everything in that columnd that is less than the subtotal
wouldn't that be everything?

Djblois
03-26-2007, 09:18 AM
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.

lucas
03-26-2007, 09:22 AM
so the numbers above the subtotal are not the figures being subtotaled?

Djblois
03-26-2007, 09:25 AM
no the subtotal is actually an average of the numbers above it.

Zack Barresse
03-26-2007, 09:26 AM
Why not just use a Pivot Table?

lucas
03-26-2007, 09:26 AM
so it's not a subtotal....

Djblois
03-26-2007, 09:48 AM
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.

Bob Phillips
03-26-2007, 09:58 AM
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))

Bob Phillips
03-26-2007, 10:03 AM
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)))

Djblois
03-26-2007, 10:09 AM
Yes, I know how to do it in a formula lucas but I want to do it in code

lucas
03-26-2007, 10:11 AM
I think poor Daniel is getting spread too thin....

Bob Phillips
03-26-2007, 10:13 AM
You're right Bob!

He is running 5 different question at the moment.

Bob Phillips
03-26-2007, 10:13 AM
Yes, I know how to do it in a formula lucas but I want to do it in code
Why? That is a class solution that I have given you, so why use code unnecessarily?

lucas
03-26-2007, 10:20 AM
You're right Bob!

He is running 5 different question at the moment.
Finally, a little humor in the mist of chaos :devil2:

Djblois
03-26-2007, 10:47 AM
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.

Bob Phillips
03-26-2007, 10:51 AM
Then I would create a template report, with all the formatting including CF, and open that from the add-in, and populate as required.

Djblois
03-26-2007, 10:53 AM
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.

Bob Phillips
03-26-2007, 10:57 AM
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.

Djblois
03-26-2007, 12:20 PM
no that is a seperate function, just for user created tables. that is why I didn't put them in the same thread.