conditional formatting question

[MagnumOpus]

I've watched every youtube video on conditional formatting, and read every tutorial plus read the help file, and I still can't get it to work! What I want to do is run a series of pairs of numbers against pairs on another sheet (same workbook) and have the fill color of those pairs that match change. Ideally there should be a date range of 7-10 days, but that's secondary. I have another spreadsheet where someone did something similar, but they used countif functions (which I couldn't figure out to save my soul). Should I use countif, countifs, nested ifs, or what??

When I try, I always keep getting error messages saying that I can't select a cell reference, and I have to use a single cell. Am I using the wrong function? I just want it to work because I have been at this all afternoon to no avail.

I'll try to make this easy if someone can help. The cell references are:
A1:K19 (array that I'd like the colors to change within)
c3:e9 AND n39 (reference range - if I could add a date function also that would be awesome but again, that's not critical.)

What I've been doing is: select the array range (a1:k19), go to conditional formatting, manage rules, new rule, "format only cells that contain", cell value = to the reference range, choose the color gray, and then I get the same error when I try to get it to work. Mega Frustrating!

It seems so simple when I watch someone else do it, but it won't work for me (I'm obviously not an expert). I'm using Excel 2007 sp2. Any help would be awesome and much appreciated.

 

[JoeMo]

"What I want to do is run a series of pairs of numbers against pairs on another sheet (same workbook)"

You can't use conditional formatting across sheets, even if they are in the same workbook.

 

[barry houdini]

You can use conditional formatting across worksheets.......you normally need to use named ranges ....

What needs to match though? A1:K19 is eleven columns, which of those need to match C3:E9 or N3:P9? Are you matching within rows?

 

[MagnumOpus]

Ok, thanks for that info. However when I move the array of pairs to the same sheet as the reference ranges, and attempt the same operation I get:

"You cannot use a direct reference to a worksheet range in a conditional formatting formula. Change the reference to a single cell, or use the reference with a worksheet function such as =sum(a1:e5)"

This is the same error that I've been getting all afternoon. I've even tried to do it using only one reference range instead of two, and I still get errors. I even tried to do it by tedious cell to cell comparison, and then drag it down the whole row, but then it changes all my values.

 

[MagnumOpus]

I've never heard of a named range. I'm gonna look that up. What needs to match is the array (a1:k19) and the reference ranges (c3:e9 AND n39). This will give you a visual:

array:

22 01 23 49 18
20 34 82 03 83
03 45 83 49 07

Reference range(s)

30 24 58 23 07
02 93 45 80 29
23 45 77 48 50

Using this example, I'd like for 07,23,45 etc. to fill in on the array since they appear in both ranges. It seems so simple, but I can't get it to work.

 

[barry houdini]

So you just want to highlight any cell in A1:K19 that matches any cell in either of the other ranges?

I'm assuming that the C3:E9 and N3:P9 ranges are on another sheet

You can name those ranges like this:

Select C3:E9 and right click
Choose "Name a range" option from the menu
In the "Name" box type a name like RangeA
Repeat for N3:P9, calling that range RangeB

Back in the "array" choose that range A1:K19 and use this formula in conditional formatting

=COUNTIF(rangeA,A1)+COUNTIF(RangeB,A1)

Choose required format

 

[MagnumOpus]

OMG!!!! It WORKS!!! Dude, you ROCK! Thank you sooooo much!!!!