Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to format rows by groups
I have a table in Excel, say, which has grade/subject/standards
columns, shown as following: subject grade standards Math 3 A Math 3 B Math 3 C Reading 3 A Reading 3 B Reading 3 C Reading 3 D Math 4 A Math 4 B Math 4 C Reading 4 A Reading 4 B Reading 4 C Reading 4 D I want to put the rows where subject="Math" & grade="3" with grey background, the rows where subject="Reading" & grade="3" with white background. Again, the rows with subject="Math" & grade="4" with grey background. In a word, the purpose is to distinguish different grade subject info by using different background colors (BTW, the number of rows for each grade subject varies, cannot be known in advance). How to do this kind of formatting in VBA macros? A further question would be: when the by groups varies, how to do it? Say, the above example I need to consider both subject and grade columns to do formatting, what if next time, I only need to distinguish the rows by subject (no grade info needs to be considered). Thank you in advance! -Wendy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to format rows by groups
Hi Wendy,
This might be an alternative and simple solution to your problem. From your question I am assuming that you are not very proficient with Excel macros and it appears that you might want to later change the criteria for your formatting. I am also assuming from the sample data that there is a minimal number of variations so it might be simpler to work it in the interactive mode with Autofilter. On the worksheet select the menu item Data, Autofilter. Click on the drop down arrow for Subject and select the required subject and repeat this for the Grade column. You will now have displayed a data set matching the Subject and Grade filters you selected. Highlight all of the displayed data (Not the column headers) and format the fill to the desired color. This is easiest with the Fill Color icon on the toolbar but can be done by selecting the menu item Format, Cells, Patterns tab and select color. Repeat the above for the next set of criteria. I tested this and it works on Excel 2002 so I assume it will work on other versions so good luck and let me know how it goes. Regards, OssieMac " wrote: I have a table in Excel, say, which has grade/subject/standards columns, shown as following: subject grade standards Math 3 A Math 3 B Math 3 C Reading 3 A Reading 3 B Reading 3 C Reading 3 D Math 4 A Math 4 B Math 4 C Reading 4 A Reading 4 B Reading 4 C Reading 4 D I want to put the rows where subject="Math" & grade="3" with grey background, the rows where subject="Reading" & grade="3" with white background. Again, the rows with subject="Math" & grade="4" with grey background. In a word, the purpose is to distinguish different grade subject info by using different background colors (BTW, the number of rows for each grade subject varies, cannot be known in advance). How to do this kind of formatting in VBA macros? A further question would be: when the by groups varies, how to do it? Say, the above example I need to consider both subject and grade columns to do formatting, what if next time, I only need to distinguish the rows by subject (no grade info needs to be considered). Thank you in advance! -Wendy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Groups of Rows | Excel Discussion (Misc queries) | |||
How to sort two groups of rows while skipping over a row | New Users to Excel | |||
How can I sort in groups of 3 rows? | Excel Worksheet Functions | |||
Reorganizing rows into groups | Excel Programming | |||
Sum rows in groups | Excel Worksheet Functions |