Conditional formatting Excel 2007/2010: Highlight the first cell in the row that contains duplicate values?
- by Nancy Prades
I have a table with hundreds of columns and rows of data; each row and column have a header. For instance, column headers are ITEM, FILE1, FILE2, FILE3, etc. and row headers are AA, BB, CC, DD, and so on.
Under conditional formatting, I used "Highlight Cells Rules" "Equal to", in order to highlight cells that have values equal to the value in another cell. In this case, my formula rule is:
Rule: Cell Value = $A$1
Applies to: =$B$3:$G$8
When I input "X" into cell A1, Excel will highlight all of the cells that have a value equal to "X", in this case, the following cells are highlighted: B3, C5, G6, and E8.
Here's my problem. The data that I am working with contains more than 100 columns and rows. I want to identify all of the ITEMS (AA, BB, CC, etc.) that contain the duplicate file "X". In order to do this I have to scroll right to left, and up and down.
Here's my question. Is there a way to use conditional formatting to add an additional rule? I want to keep the current rule, but I also want the row header to be highlighted if any of the cells in that row contain a value equal to "x". In this case, I want AA, CC, DD, and FF to also be highlighted.
Is this possible? I've spent days trying to figure this out - and no luck. Any help would be appreciated! :)
Nancy
A B C D E F G
1 X
2 ITEM FILE1 FILE2 FILE3 FILE4 FILE5 FILE 6
3 AA x t y u d w
4 BB r y a b k d
5 CC y x f u i g
6 DD t v b d f x
7 EE e w y s l n
8 FF w u n x e m
