Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.