If / Then based off conditional format

[Guest]

Is there VB code that will let me do the following:
If the cell is color green (turns green from conditional format)
Then paste this formula into it "=IF(AC23=J23,J23*0.5,IF(D23="
",VLOOKUP(C23,'2006 IP Payer Mix on
Settled'!H:I,2,FALSE)*K23,IF(B23="A",VLOOKUP(D23,'2006 IP
Template'!B:C,2,FALSE),IF(B23="B",VLOOKUP(D23,'2006 IP
Template'!B:G,6,FALSE),IF(B23="h",VLOOKUP(C23,'2006 IP Payer Mix on
Settled'!H:I,2,FALSE)*K23,IF(B23="d",VLOOKUP(D23,'2006 IP
Template'!B:Y,10,FALSE),IF(C23="D05",VLOOKUP(D23,'2006 IP
Template'!B:Y,11,FALSE))))))))"

Or If the cell is color yellow (turns yellow from conditional format)
Then paste this formula into it "=IF(AC1550=J1550,J1550*0.5,IF(D1550="
",VLOOKUP(C1550,'2007 IP Payer Mix on
Settled'!H:I,2,FALSE)*K1550,IF(B1550="A",VLOOKUP(D1550,'2007 IP
Template'!B:C,2,FALSE),IF(B1550="B",VLOOKUP(D1550,'2007 IP
Template'!B:G,6,FALSE),IF(B1550="h",VLOOKUP(C1550,'2007 IP Payer Mix on
Settled'!H:I,2,FALSE)*K1550,IF(B1550="d",VLOOKUP(D1550,'2007 IP
Template'!B:Y,10,FALSE),IF(C1550="D05",VLOOKUP(D1550,'2007 IP
Template'!B:Y,11,FALSE))))))))"

In advance thanks for your help!

 

[Trevor Shuttleworth]

Why not just test for the same condition that sets the cell green or yellow
?

But, if you paste a formula into the cell, the value may change and the
condition might not be met.

Regards

Trevor

 

[Guest]

Assuming that the cell you wanted to test is A1, you'll need to do something
like this:

With Range("A1")
If .Interior.Color = vbGreen Then
.Formula = "YOUR_FORMULA_FOR_GREEN_HERE"
ElseIf .Interior.Color = vbYellow Then
.Formula = "YOUR_FORMULA_FOR_YELLO_HERE"
End If
End With

 

[Trevor Shuttleworth]

I don't think that will work because Conditional Formatting isn't setting
the Interior Colour.

Regards

Trevor

 

[Guest]

The conditional format is set from looking at a differnet column. Turns
green by conditional format formula "=($F24)>39082". I can't add it to the
formula in the cell because the formula has already met it's limit of (()))).
Does that make sense?

 

[Trevor Shuttleworth]

So your VBA code would look something like:

If Range("F24") > 39082 Then
With Range("xxxx")
.Formula = "=IF(AC23=J23,J23*0.5,IF(D23="""",VLOOKUP(C23,'2006 IP Payer
Mix on
Settled'!H:I,2,FALSE)*K23,IF(B23=""A"",VLOOKUP(D23,'2006 IP
Template'!B:C,2,FALSE),IF(B23=""B"",VLOOKUP(D23,'2006 IP
Template'!B:G,6,FALSE),IF(B23=""h"",VLOOKUP(C23,'2006 IP Payer Mix on
Settled'!H:I,2,FALSE)*K23,IF(B23=""d"",VLOOKUP(D23,'2006 IP
Template'!B:Y,10,FALSE),IF(C23=""D05"",VLOOKUP(D23,'2006 IP
Template'!B:Y,11,FALSE))))))))"

End With

Regards

Trevor