how to format rows by groups

[wlv]

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

 

[OssieMac]

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