Q:

Checking cell value dynamically

What I'm doing is a simple map on one tab of an office space that has all the cubicals laid out.  On this map, I have conditional formatting checking another table of listen computers for the last time they were updated/maintained.  It works as it stands, but we are moving into a new building and the map (which is already done) has WAY more cubes than last time.  So I'm trying to figure out a more efficient way to do this task.
Here is the code:
=AND(VLOOKUP("CCA C1",LOCATION,3,FALSE)<>"",VLOOKUP("CCA C1",LOCATION,3,FALSE)<TODAY()-80)

So as it sits, it works fine.  But what I'm trying to do is change the "CCA C1" to simply read the contents of the cell it's formatting so I can just format an entire range of cells rather than doing it cell by cell.  The "CCA C1" is in the location of the second sheet (it's a named range).  So this checks that entire range for "CCA C1" and checks if the date a couple columns over in that row is within 80 days, if it's not it highlights the cell in the map in red reminding us we need to check the computer.  But what I would like to do is simply put "CCA C1" in the cell in the map (which is already is labeled), and have this check for the value of the cell it's formatting and look for that value the same way it's looking now.  Just without me having to put "CCA C1" in the formula, I'd like to say something like this:
=AND(VLOOKUP(CURRENT CELL VALUE,LOCATION,3,FALSE)<>"",VLOOKUP(CURRENT CELL VALUE,LOCATION,3,FALSE)<TODAY()-80)

Make sense?  Anyone know of a way to do this?  Otherwise I have to conditionally format each individual cell with the value manually rather than just format all the cells with the same formatting and have the formula check the contents of the cell for what to look for in the location range of the other sheet.
And to clarify, I know that I can put in the actual cell, such as E3, but then I still have to manually change the formula for each cell which defeats the purpose.  I want to just say current cell or something like that.  I have 3 conditional formats for each cell, I have around 100 cells to be formatted, so rather than having 300 formats I have to put in, I'd love to just do 3.  Not sure if this is possible, that's why I'm asking :)

A:

Just replace "CCA C1" with the address of the first cell in the range of cells with the conditional formatting. Assuming your conditional formatting starts in B2:
=AND(VLOOKUP(B2,LOCATION,3,FALSE)<>"",VLOOKUP("CCA C1",LOCATION,3,FALSE)<TODAY()-80)

EDIT: As I commented, I'm not sure I understand the issue, but if I do, you need to enter the range of cells with CF in the applies to range of the CF dialog, rather than copying and using Paste Special:

Note that this works with the merged cells.
You will need to adjust the applies to range as you add more computers, etc., but the same formula will work.

