Programming Formula for Cond. Format yields different formula

[Guest]

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

 

[Jim Cone]

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"
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

 

[Guest]

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

 

[Mark Lincoln]

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

 

[Mark Lincoln]

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.

 

[Mark Lincoln]

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

 

[Guest]

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]

Glad to help, Will. And I learned something myself!

Mark Lincoln

 

[Mark Lincoln]

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