2

I have two column ranges: C410:C1100 and M5:M100. I want to iterate through the C range, one cell at a time and try to find at least one match in the entire M range. If a match is found, I want to color the background of the cell in the C range yellow or something (honestly the format isn't that important).

I have struggled to find a solution to do this via a formula. I thought about VLOOKUP but wasn't sure.

pnuts
  • 58,317
  • 11
  • 87
  • 139
dave111
  • 173
  • 1
  • 2
  • 13

1 Answers1

5

Please try selecting ColumnC HOME > Conditional Formatting, New Rule..., Use a formula to determine which cells to format, Format values where this formula is true::

=MATCH(C1,M$5:M$100,0)

Format..., select yellow, OK, OK.

Go to Manage Rules and limit the Applies to range as you wish.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Ok that is correct for what I asked for...however i realize I missed 1 more part of my question. So the match is actually 2 fold. If I find a match in the M range AND if the column D value = 0 (in the same row right next to the C cell) then we do the highlight. Any way to 2 matches from one cell? – dave111 Mar 19 '15 at 22:08
  • 1
    I will try tomorrow and let you know, been at the office for 11 hrs lol – dave111 Mar 19 '15 at 23:31