| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming Formula for Cond. Format yields different formula
I'm trying to set conditional formatting constraints programmatically. I
started out constructing the conditions equation from variables but, in troubleshooting, have simplified to the point where I'm basically just writing the string directly - AND IT STILL DOESN'T WORK!!! My code is below. The old conditional formatting gets deleted and new formatting goes to the right cell. It's just that the wrong formula (and not always the same) is there when I check via Format/Cells. For example, for the first condition formula I've seen both "=$J2 = U$2" and "=$J2 = X$2" (instead of "=$J3 = U$2"). The second conditional formula seems to always follow the first as far as what deviates from what I'm trying to apply. With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Any ideas? Thanks, Will |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming Formula for Cond. Format yields different formula
Have you tried it with absolute references for both columns and rows?... Formula1:="=$J$3 = $U$2" -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "roadkill" wrote in message I'm trying to set conditional formatting constraints programmatically. I started out constructing the conditions equation from variables but, in troubleshooting, have simplified to the point where I'm basically just writing the string directly - AND IT STILL DOESN'T WORK!!! My code is below. The old conditional formatting gets deleted and new formatting goes to the right cell. It's just that the wrong formula (and not always the same) is there when I check via Format/Cells. For example, for the first condition formula I've seen both "=$J2 = U$2" and "=$J2 = X$2" (instead of "=$J3 = U$2"). The second conditional formula seems to always follow the first as far as what deviates from what I'm trying to apply. With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Any ideas? Thanks, Will |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming Formula for Cond. Format yields different formula
Since you asked, I tried it and it appears that the formula comes through
unchanged. Unfortunately, I need for relative reference. Any idea why absolute is okay but relative is not? Will "Jim Cone" wrote: Have you tried it with absolute references for both columns and rows?... Formula1:="=$J$3 = $U$2" -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "roadkill" wrote in message I'm trying to set conditional formatting constraints programmatically. I started out constructing the conditions equation from variables but, in troubleshooting, have simplified to the point where I'm basically just writing the string directly - AND IT STILL DOESN'T WORK!!! My code is below. The old conditional formatting gets deleted and new formatting goes to the right cell. It's just that the wrong formula (and not always the same) is there when I check via Format/Cells. For example, for the first condition formula I've seen both "=$J2 = U$2" and "=$J2 = X$2" (instead of "=$J3 = U$2"). The second conditional formula seems to always follow the first as far as what deviates from what I'm trying to apply. With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Any ideas? Thanks, Will |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming Formula for Cond. Format yields different formula
Will,
I had to play around with this for a while, but I finally figured it out. Your CF formulas are being entered using relative addressing based on the position of the active cell. It does not matter what you do in terms of coding absolute or relative references in the formulas. The solution, though, is simple. Just add ".Select" before the CF code. As in your example: With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .Select .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Each individual cell, being the active cell, will have the formulas entered relative to itself. Mark Lincoln On Jul 2, 1:32 pm, roadkill wrote: I'm trying to set conditional formatting constraints programmatically. I started out constructing the conditions equation from variables but, in troubleshooting, have simplified to the point where I'm basically just writing the string directly - AND IT STILL DOESN'T WORK!!! My code is below. The old conditional formatting gets deleted and new formatting goes to the right cell. It's just that the wrong formula (and not always the same) is there when I check via Format/Cells. For example, for the first condition formula I've seen both "=$J2 = U$2" and "=$J2 = X$2" (instead of "=$J3 = U$2"). The second conditional formula seems to always follow the first as far as what deviates from what I'm trying to apply. With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Any ideas? Thanks, Will |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming Formula for Cond. Format yields different formula
I need to clarify something. The sentence "It does not matter what
you do in terms of coding absolute or relative references in the formulas" is absolutely false; absolute references will be maintained. But to get the same relative references in every cell you do have to Select each one individually. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming Formula for Cond. Format yields different formula
A further clarification: I had forgotten, while working out the
solution, that you mentioned needing relative references. If you wanted the same CF formula in each cell you would just use absolute references as suggested by Jim. I believe you'll get the results you want by first selecting the cell from which all the relative references are based, then run your With- End With code *without* the .Select line. Sorry for any confusion. Mark Lincoln On Jul 8, 12:29 pm, Mark Lincoln wrote: Will, I had to play around with this for a while, but I finally figured it out. Your CF formulas are being entered using relative addressing based on the position of the active cell. It does not matter what you do in terms of coding absolute or relative references in the formulas. The solution, though, is simple. Just add ".Select" before the CF code. As in your example: With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .Select .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Each individual cell, being the active cell, will have the formulas entered relative to itself. Mark Lincoln On Jul 2, 1:32 pm, roadkill wrote: I'm trying to set conditional formatting constraints programmatically. I started out constructing the conditions equation from variables but, in troubleshooting, have simplified to the point where I'm basically just writing the string directly - AND IT STILL DOESN'T WORK!!! My code is below. The old conditional formatting gets deleted and new formatting goes to the right cell. It's just that the wrong formula (and not always the same) is there when I check via Format/Cells. For example, for the first condition formula I've seen both "=$J2 = U$2" and "=$J2 = X$2" (instead of "=$J3 = U$2"). The second conditional formula seems to always follow the first as far as what deviates from what I'm trying to apply. With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Any ideas? Thanks, Will- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming Formula for Cond. Format yields different formula
Mark
That certainly was not intuitive. You would think (or at least I would) that writing a very specific formula for the CF would have literally written exactly that formula. Obviously not so, and it looks like your solution worked. Thanks a bunch! Will "Mark Lincoln" wrote: A further clarification: I had forgotten, while working out the solution, that you mentioned needing relative references. If you wanted the same CF formula in each cell you would just use absolute references as suggested by Jim. I believe you'll get the results you want by first selecting the cell from which all the relative references are based, then run your With- End With code *without* the .Select line. Sorry for any confusion. Mark Lincoln On Jul 8, 12:29 pm, Mark Lincoln wrote: Will, I had to play around with this for a while, but I finally figured it out. Your CF formulas are being entered using relative addressing based on the position of the active cell. It does not matter what you do in terms of coding absolute or relative references in the formulas. The solution, though, is simple. Just add ".Select" before the CF code. As in your example: With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .Select .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Each individual cell, being the active cell, will have the formulas entered relative to itself. Mark Lincoln On Jul 2, 1:32 pm, roadkill wrote: I'm trying to set conditional formatting constraints programmatically. I started out constructing the conditions equation from variables but, in troubleshooting, have simplified to the point where I'm basically just writing the string directly - AND IT STILL DOESN'T WORK!!! My code is below. The old conditional formatting gets deleted and new formatting goes to the right cell. It's just that the wrong formula (and not always the same) is there when I check via Format/Cells. For example, for the first condition formula I've seen both "=$J2 = U$2" and "=$J2 = X$2" (instead of "=$J3 = U$2"). The second conditional formula seems to always follow the first as far as what deviates from what I'm trying to apply. With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Any ideas? Thanks, Will- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming Formula for Cond. Format yields different formula
Glad to help, Will. And I learned something myself!
Mark Lincoln On Jul 9, 10:06 am, roadkill wrote: Mark That certainly was not intuitive. You would think (or at least I would) that writing a very specific formula for the CF would have literally written exactly that formula. Obviously not so, and it looks like your solution worked. Thanks a bunch! Will "Mark Lincoln" wrote: A further clarification: I had forgotten, while working out the solution, that you mentioned needing relative references. If you wanted the same CF formula in each cell you would just use absolute references as suggested by Jim. I believe you'll get the results you want by first selecting the cell from which all the relative references are based, then run your With- End With code *without* the .Select line. Sorry for any confusion. Mark Lincoln On Jul 8, 12:29 pm, Mark Lincoln wrote: Will, I had to play around with this for a while, but I finally figured it out. Your CF formulas are being entered using relative addressing based on the position of the active cell. It does not matter what you do in terms of coding absolute or relative references in the formulas. The solution, though, is simple. Just add ".Select" before the CF code. As in your example: With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .Select .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Each individual cell, being the active cell, will have the formulas entered relative to itself. Mark Lincoln On Jul 2, 1:32 pm, roadkill wrote: I'm trying to set conditional formatting constraints programmatically. I started out constructing the conditions equation from variables but, in troubleshooting, have simplified to the point where I'm basically just writing the string directly - AND IT STILL DOESN'T WORK!!! My code is below. The old conditional formatting gets deleted and new formatting goes to the right cell. It's just that the wrong formula (and not always the same) is there when I check via Format/Cells. For example, for the first condition formula I've seen both "=$J2 = U$2" and "=$J2 = X$2" (instead of "=$J3 = U$2"). The second conditional formula seems to always follow the first as far as what deviates from what I'm trying to apply. With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Any ideas? Thanks, Will- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Programming Formula for Cond. Format yields different formula
Will,
I was thinking about the behavior of the code and came to realize that the way the CFs are being written corresponds to using the Format Painter to copy formats across a range. If there are CFs with relative references in the format being "painted," you will see the same behavior. I agree that one wouldn't normally expect VBA to treat the active cell as the CF "source" cell. I wonder how the programmers decided to do it that way; there might have been some very interesting discussions. Mark Lincoln On Jul 9, 10:06 am, roadkill wrote: Mark That certainly was not intuitive. You would think (or at least I would) that writing a very specific formula for the CF would have literally written exactly that formula. Obviously not so, and it looks like your solution worked. Thanks a bunch! Will "Mark Lincoln" wrote: A further clarification: I had forgotten, while working out the solution, that you mentioned needing relative references. If you wanted the same CF formula in each cell you would just use absolute references as suggested by Jim. I believe you'll get the results you want by first selecting the cell from which all the relative references are based, then run your With- End With code *without* the .Select line. Sorry for any confusion. Mark Lincoln On Jul 8, 12:29 pm, Mark Lincoln wrote: Will, I had to play around with this for a while, but I finally figured it out. Your CF formulas are being entered using relative addressing based on the position of the active cell. It does not matter what you do in terms of coding absolute or relative references in the formulas. The solution, though, is simple. Just add ".Select" before the CF code. As in your example: With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .Select .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Each individual cell, being the active cell, will have the formulas entered relative to itself. Mark Lincoln On Jul 2, 1:32 pm, roadkill wrote: I'm trying to set conditional formatting constraints programmatically. I started out constructing the conditions equation from variables but, in troubleshooting, have simplified to the point where I'm basically just writing the string directly - AND IT STILL DOESN'T WORK!!! My code is below. The old conditional formatting gets deleted and new formatting goes to the right cell. It's just that the wrong formula (and not always the same) is there when I check via Format/Cells. For example, for the first condition formula I've seen both "=$J2 = U$2" and "=$J2 = X$2" (instead of "=$J3 = U$2"). The second conditional formula seems to always follow the first as far as what deviates from what I'm trying to apply. With ActiveSheet.Range(Cells(PCTROW, intColN + 1), Cells(PCTROW, intCol1 + intNumWks - 1)) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$2" .FormatConditions(1).Interior.ColorIndex = RED .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$J3 = U$1" .FormatConditions(2).Interior.ColorIndex = BLUE .Interior.ColorIndex = GREEN End With Any ideas? Thanks, Will- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF statement that yields a value based on a formula | Excel Worksheet Functions | |||
| Formula yields State, not Numbers! | New Users to Excel | |||
| Make the result of a formula red (cannot be done in cond. format.) | Excel Discussion (Misc queries) | |||
| How do I use cond format formula to set cell text and color? | Excel Discussion (Misc queries) | |||
| Formula Yields Slow response | Excel Programming | |||
Linear Mode
