Refence another workbook based on value in a cell

I am sure this easy but it's driving me mad.

I have cells in a worksheet in workbook A that compares values within it to an identically named sheet in workbook B.

At the moment I am using "helper" cells to identify a difference between the two workbooks/worksheets/cells and then using that helper cell to crive some conditional formatting.

What I want to do is drive the conditional formatting directly.

So what I intend is a cell on the worksheet in workbook B (let's say A1) that contains a filename for workbook A.
The conditional format would need to compare a cell in the current workbook (workbook B) with the same cell in the same named worksheet in workbook A.

Then, as time progresses and I create workbook C, I would change the cell A1 in workbook C so it referred to workbook B.

In case it's not clear, I'm trying to spot and track changes between different versions of a workbook.

The "helper" for comparing cell A8 currently looks something like ...
=IF(A8<>'C:\Dir1\Dir2\[workbook V1.02.xls]worksheetA'!A8,1,0)

I want this in a cond format in A8 to save the use of helper cells (which have a habit of being overwritten etc by other users).

Thanks in advance.

You cannot refer in Conditional formatting to the other workbook.

Thanks. Small change to my plans then ...

How about referencing the cells in the other workbook within my "helper" cells using a parameterised filename.

So I'd have a cell with the filename in it and then be able to change that value to change where the helper cell looked for the comparison?

I'm now falling back on VBA to try and help me.

The path of the "old" file is fixed.
The filename and sheet name in the "old" file are recorded in cell B5.
The cell I am comparing is cell A8.
The "helper" field that will be set by it is in J8.

I have a macro (borrowed from MSP77079 in another thread) that does the following ...
strFormula = "=IF(RC[-9]<>'P:\S\SUPRMAR\Data Migration\Data Workstream\Target Definitions\"
strFormula = strFormula & Range("b5").Value
strFormula = strFormula & "! A8,1,0)"

Range("j8").FormulaR1C1 = strFormula

The "A8" in the last addition to strFormula is being written to the cell with single quotes around it. If I replace the "A8" with "XX" then the XX is written without the single quotes so I am assuming it is something to do with the fact that "A8" is a cell.

Anybody help?

Also - I need to repeat this for a series of cells - any good ideas on how to repeat for an unknown (in advance) number of rows?


At the point immediately before the "RANGE" write statement, the variable is 100% correct. It is the write the cell that is causing the problem. When I tried changing "A8" to "C8" the write translated that to the whole of column H (the eigth column).
Can anyone advise me before I throw the PC (or myself) off a naerby bridge?