Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question

You can add another conditional formatting for the Cells in the Item column, when creating the rule pick "Use a formula to determine which cells to format", and use this (e.g. starting from A3):

=MATCH($A$1,B3:G3,0)>0
share|improve this answer
    
+1. Seems that the appropriate range should be B3:G3 instead of B3:E3. – wilson Oct 15 '12 at 10:14
    
@wilson, Yes thank you. That was my test range. – Michael Oct 15 '12 at 10:19
1  
+1 In fact it will work without >0. MATCH returns a positive number or an error, the positive number will trigger the formatting – barry houdini Oct 15 '12 at 14:06
    
@barryhoudini, yea, I just like keeping logical expressions explicit. – Michael Oct 15 '12 at 23:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .