Highlighting A Row Based on Cell Value Read from Another Document

[CJ Melo]

I would like to highlight rows in Excel 2003 based on the entry in a column
which happens to be a student's name. I would like to be able to have excel
check a list of names automatically and highlight all the rows that pertain
to that student so I don't have to type in each name in the conditional
formatting box, which I understand is limited to 3 criteria.

I know nothing about VBA or any other kind of programming. Is this possible?

 

[Tom Hutchins]

You can do this without VBA. On another sheet (call it Sheet2), list the
student names the way they appear on the main sheet. On Sheet2, select all
the names, then name that range Students (In the Name Box, which is where the
cell's address is displayed next to the Formula Bar). In another cell, on
either the main sheet or on Sheet2, create a dropdown list in a cell using
Data Validation. In the Data Validation dialog, select List for the Allow
option, and in the Source box enter =Students. Name the cell with the
dropdown list CurrStudent. On the main sheet, select all the cells. Add
conditional formatting where this formula is true:
=(INDIRECT(ADDRESS(ROW(),1))=CurrStudent)

Pick the formatting you want applied, then click OK to close the Conditional
Formatting dialog. When you select a name from the CurrStudent dropdown, all
the rows with that sudent's name will be highlighted.

My conditional formatting formula assumes the students' names are in column
A (represented by the 1, which is the column number). Edit as needed.

Hope this helps,

Hutch

 

[JLatham]

I'm not sure exactly how you want this to work, but here's a non-VBA solution
using conditional formatting that will highlight cells based on the name in a
column matching the entry in another cell on the sheet.

In this example, "another cell" is going to be A1, and we assume that your
student's names are in column C. For the example your data entry area is
presumed to be from column A over to column G. Change these things as
required.

Start by selecting the cell in the upper left corner of your data area, A2.
Then continue to select all the cells in the data area (A2 to G9 for this
example).

Choose Format --> Conditional Formatting.
In the conditional formatting dialog, choose "Formula Is" and enter this
formula
=$C2=$A$1
then set your formatting to to highlight the rows when the name in column C
matches the name in cell A1.
NOTE: change $C to the column with the names in it.
Click [OK] to accept the formatting and [OK] again to close the conditional
formatting dialog.

Type a name that matches one of your names in the list and the cells on
that/those rows with that name should be highlighted.