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 -