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 -