How do I compare 2 cells in 2 spreadsheets,causing conditional format 2

[Smoothas]

Hello,

I have 2 spreadsheets ( Spreadsheet A and Spreadsheet B).
Spreadsheet A has lots of infromation in it ( one person per row ). There are 2 columns I am intrested in: Column B lists the persons unique ID, while Column G list there cash balance.

Spreadsheet B lists the Unique ID ( In column A ) and there Credit Limit ( In Column B ).

What I'm trying to do, and failing at the moment, is, in Spreadsheet A, have a macro that looks at the Unqiue ID in spreadsheet b, when it finds a match, looks at the amount in Spreadsheet B: Column B , and if it is equal to, or less than the Value in Spreadsheet A:Column G, Change the Font of that column G cell to Bold and red, else move on till the end of Spreadsheet A.

Any help, gratefully received

 

[SkipVought]

Hi,

Using CF, you cannot reference another sheet.

However, if you use UNIQUE Named Ranges on Sheet B, you can reference the Named Range on Sheet A in your CF, like...
[tt]
=G2>INDEX(Credit_Limit,MATCH(A2,Unique_ID,0),1)
[/tt]
assuming that A2 has an ID and G2 has a Credit Limit Value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Smoothas]

hi,
Thans for the reply.
I've tried the code, but I get #NAME error in the cell.
I've tried tweaking it you use with an "external" spreadsheet / workbook, but I still get the same prob.

I've attached some cutdown versions of the files i'm trying to perform this on, I hope it helps.

 

[Smoothas]

Hello,
I've been tinkering with the code you posted, and I've come with the following :-

=IF(B:B='[ClientCreditLimit.xls]Sheet1'!$A:$A,'[ClientCreditLimit.xls]Sheet1'!$B$9)

Which works for 1 cell, but I then have to change the cell ref ( ie $b$10 ) for it to work on the next row, etc.

Is there a way of chaning this to the row number thats been match is automatically used?, so i can add this into evey cell in column M in the Fees.xls sheet (I can then perform condition formatting on the Balance column depending on the content of the M cell )

Thanks again
Gez

 

[PHV]

What happens if you replace $B$9 with $B9 and then copy down the formula ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Smoothas]

D'oh, why is' always the easy things I miss out.

Thats works a treat.
Thanks PHV

 

[Smoothas]

Hello,
I've trying to implement this into a macro now, and I have it pasting to to one cell in column M, then copying it, then it dosn't seem to go any further doen that column to the last row enetered.
This is the code I am using :-

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 29/10/2008
'

'
Dim All_Data As Range
Dim R As Range, s As String, C As Range, lRow As Long

Set All_Data = Range([M2], Cells(Cells.Rows.Count, "M").End(xlUp))
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(C[-11]='[ClientCreditLimit.xls]Sheet1'!C1,'[ClientCreditLimit.xls]Sheet1'!RC2)"
Range("M2").Select
Selection.Copy
For Each R In All_Data
For Each C In Range(R, R.End(xlToRight))
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next
Next R
End Sub

Again , it's problably something simple that I cannot see.

Thank in advance

Gez

 

[Smoothas]

Hello,

just incase anyone else is following this, or stumbles across this later, the code that now works for me :-

Dim LastCell As Integer
Range("m1") = "Credit Limit"
Range("m2").FormulaR1C1 = "=IF(C[-11]='[ClientCreditLimit.xls]Sheet1'!C1,'[ClientCreditLimit.xls]Sheet1'!RC2)"
LastCell = Range("f65535").End(xlUp).Row
'change the above line to the right column letter that determines
'the last row of data that determines how far down to copy
'the formula
Range("m2").Copy Destination:=Range("m2").Resize(LastCell, 1)