If / Then based off conditional format

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
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
 
I don't think that will work because Conditional Formatting isn't setting
the Interior Colour.

Regards

Trevor
 
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?
 
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
 
Back
Top