psionic
06-03-2008, 08:47 PM
Hi guys me again :hi:
:motz2:A really,really tricky and definitly frustating VBA solution for me to solve a problem. :banghead:
Got a spreadsheet with over 1000 rows of data with 20+ columns of data (from a database) and I have coloured the cells according to some administrative decisions or criteria.
* If someone can show me using Pivot tables for coloured cells would be great, however I'm sceptial that there isn't one available. I use Excel 2002 not 2007, mind you. *
Anyway, now I wanted to produce a (pivot sort of like) report listing by row showing the different colours (I used 4) and another listing by column showing the different colour ( I used 4).
So for me to discuss this subject clearly, let's say I create a fictional speadsheet of using RANDBETWEEN(1,100) function as data and contains headings like Name, Far, Near, Small and Big. Also contains a row describing the data so Joe, John and Jess will do. NOTE: Duplicates names are included so I also need to find unique names for the report when processing rows as well. Columns are unique anyway, thanks heavens!!
A B C D E
Name Far Near Small Big
1 Joe 54 23 68 75
2 John 60 72 15 89
3 Jess 99 12 54 32
4 John 13 79 33 69
I made a sub routine using a For each cell loop of the entire range and change the background colour of the cell. So any number within tens (10-19) change background colour as orange and within seventies (70-79)change background colour as blue - Easy and straightforward.
My muscle tension rises when I want to make a report (of the results) that lists all the colours for the above sheet including white (the default colour) with a total processed so I can see a some sort of distrubtion in what colour dominates the rest.
By Row
Name Orange Blue White Total
Joe 0 1 3 4
John 2 2 4 8
Jess 1 0 3 4
TOTAL 3 3 10 16
By Column
Field Orange Blue White Total
Name 0 0 4 4
Far 1 0 3 4
Near 1 2 1 4
Big 1 0 3 4
Small 1 0 3 4
TOTAL 4 2 14 20
Any assistance or clarification would be a smashing thing. :bow:
My previous attempts in doing this problem was a make a class that is put into a collection but found out I didn't know how to update an object in a collection so I gave up on that! GRrrr!
I thought of using dynamic multidimensional array but didn't know how I could seprate the colours while processing the rows knowing I need unique rows.
Hence my plea!
With thanks and warm regards,
Psionic
:motz2:A really,really tricky and definitly frustating VBA solution for me to solve a problem. :banghead:
Got a spreadsheet with over 1000 rows of data with 20+ columns of data (from a database) and I have coloured the cells according to some administrative decisions or criteria.
* If someone can show me using Pivot tables for coloured cells would be great, however I'm sceptial that there isn't one available. I use Excel 2002 not 2007, mind you. *
Anyway, now I wanted to produce a (pivot sort of like) report listing by row showing the different colours (I used 4) and another listing by column showing the different colour ( I used 4).
So for me to discuss this subject clearly, let's say I create a fictional speadsheet of using RANDBETWEEN(1,100) function as data and contains headings like Name, Far, Near, Small and Big. Also contains a row describing the data so Joe, John and Jess will do. NOTE: Duplicates names are included so I also need to find unique names for the report when processing rows as well. Columns are unique anyway, thanks heavens!!
A B C D E
Name Far Near Small Big
1 Joe 54 23 68 75
2 John 60 72 15 89
3 Jess 99 12 54 32
4 John 13 79 33 69
I made a sub routine using a For each cell loop of the entire range and change the background colour of the cell. So any number within tens (10-19) change background colour as orange and within seventies (70-79)change background colour as blue - Easy and straightforward.
My muscle tension rises when I want to make a report (of the results) that lists all the colours for the above sheet including white (the default colour) with a total processed so I can see a some sort of distrubtion in what colour dominates the rest.
By Row
Name Orange Blue White Total
Joe 0 1 3 4
John 2 2 4 8
Jess 1 0 3 4
TOTAL 3 3 10 16
By Column
Field Orange Blue White Total
Name 0 0 4 4
Far 1 0 3 4
Near 1 2 1 4
Big 1 0 3 4
Small 1 0 3 4
TOTAL 4 2 14 20
Any assistance or clarification would be a smashing thing. :bow:
My previous attempts in doing this problem was a make a class that is put into a collection but found out I didn't know how to update an object in a collection so I gave up on that! GRrrr!
I thought of using dynamic multidimensional array but didn't know how I could seprate the colours while processing the rows knowing I need unique rows.
Hence my plea!
With thanks and warm regards,
Psionic