|
If / Then based off conditional format
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,'200 6 IP Template'!B:G,6,FALSE),IF(B23="h",VLOOKUP(C23,'200 6 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(D 1550,'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(D 1550,'2007 IP Template'!B:Y,10,FALSE),IF(C1550="D05",VLOOKUP(D15 50,'2007 IP Template'!B:Y,11,FALSE))))))))" In advance thanks for your help! |
If / Then based off conditional format
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 "bill ch" wrote in message ... 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,'200 6 IP Template'!B:G,6,FALSE),IF(B23="h",VLOOKUP(C23,'200 6 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(D 1550,'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(D 1550,'2007 IP Template'!B:Y,10,FALSE),IF(C1550="D05",VLOOKUP(D15 50,'2007 IP Template'!B:Y,11,FALSE))))))))" In advance thanks for your help! |
If / Then based off conditional format
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 "bill ch" wrote: 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,'200 6 IP Template'!B:G,6,FALSE),IF(B23="h",VLOOKUP(C23,'200 6 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(D 1550,'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(D 1550,'2007 IP Template'!B:Y,10,FALSE),IF(C1550="D05",VLOOKUP(D15 50,'2007 IP Template'!B:Y,11,FALSE))))))))" In advance thanks for your help! |
If / Then based off conditional format
I don't think that will work because Conditional Formatting isn't setting
the Interior Colour. Regards Trevor "Vergel Adriano" wrote in message ... 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 "bill ch" wrote: 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,'200 6 IP Template'!B:G,6,FALSE),IF(B23="h",VLOOKUP(C23,'200 6 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(D 1550,'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(D 1550,'2007 IP Template'!B:Y,10,FALSE),IF(C1550="D05",VLOOKUP(D15 50,'2007 IP Template'!B:Y,11,FALSE))))))))" In advance thanks for your help! |
If / Then based off conditional format
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" wrote: 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 "bill ch" wrote in message ... 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,'200 6 IP Template'!B:G,6,FALSE),IF(B23="h",VLOOKUP(C23,'200 6 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(D 1550,'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(D 1550,'2007 IP Template'!B:Y,10,FALSE),IF(C1550="D05",VLOOKUP(D15 50,'2007 IP Template'!B:Y,11,FALSE))))))))" In advance thanks for your help! |
If / Then based off conditional format
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,'200 6 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,'2 006 IP Template'!B:G,6,FALSE),IF(B23=""h"",VLOOKUP(C23,'2 006 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 "bill ch" wrote in message ... 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" wrote: 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 "bill ch" wrote in message ... 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,'200 6 IP Template'!B:G,6,FALSE),IF(B23="h",VLOOKUP(C23,'200 6 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(D 1550,'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(D 1550,'2007 IP Template'!B:Y,10,FALSE),IF(C1550="D05",VLOOKUP(D15 50,'2007 IP Template'!B:Y,11,FALSE))))))))" In advance thanks for your help! |
| All times are GMT +1. The time now is 11:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com