Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > If / Then based off conditional format
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default 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!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,089
Default 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!


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 857
Default 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!
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,089
Default 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!


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default 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!



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,089
Default 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!





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Format based on sub-string BaseballFan Excel Discussion (Misc queries) 3 April 4th 09 10:04 PM
Conditional Format Based on Date Ron A.[_2_] Excel Worksheet Functions 3 March 24th 09 08:09 PM
Conditional Format a row based on a cell? WRCH5 Excel Worksheet Functions 1 June 18th 07 03:25 AM
Conditional Format based on other cell [email protected] Excel Discussion (Misc queries) 1 March 1st 07 02:20 AM
Conditional Format based on other cell's value Stella Excel Worksheet Functions 1 June 23rd 06 06:46 PM


All times are GMT +1. The time now is 06:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement