Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting A Row Based on Cell Value Read from Another Document
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting A Row Based on Cell Value Read from Another Document
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 "CJ Melo" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting A Row Based on Cell Value Read from Another Document
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. "CJ Melo" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make an execl cell A3 read-only based on A4? | Excel Discussion (Misc queries) | |||
How did my document become read-only? | Excel Worksheet Functions | |||
Highlighting rows based on one cell | Excel Worksheet Functions | |||
How to create a formatted 'readable' document based on Excel document? | Excel Discussion (Misc queries) | |||
document may be encrypred or read only | Excel Discussion (Misc queries) |