One of the problems that I see is that after you use:

With rngCond

all of the .Cells( ) references are relative to the rngCond address, i.e.
Cells(2, 2) would not be Range("B2"), but would be down two, right two from
the rngCond address. So you might not be designating the cells that you
think you are designating.

"J Streger" wrote:

I have the following procedure that runs when someone opens up a sheet and it
needs to be upgraded. I am trying to upgrade conditional formats. When I run
this code I checked and just before and after I set the conditional format,
sForm is equal to what I want "=AND($C218=TechOpsName, $D218=ContractName)"

When I check the cell immediately after I add the format, the CF formula
that was added is: "=AND($C428=TechOpsName, $D428=ContractName)"

I add this to another sheet that is an Exact copy of the previous sheet, and
the CF formula is: "=AND($C217=TechOpsName, $D217=ContractName)"

I'm not moving the cell, and have not copied it yet. This is just the cell
I'm adding it to for the first time. It's probably something simple, but I
cannot figure out why the row # is being forcibly shifted. I've tested this
on blank cells and cells with conditional formats and the same effect. Any
ideas what could be causing this?

*All named ranges in code are a single non-merged cell

Dim wsInput As Worksheet
Dim rngCond As Range
Dim sForm As String

For Each wsInput In wbUpgrade.Worksheets

'Upgrade Conditional Formats on all sheets
With wsInput
Set rngCond = .Range(.Range("WorkloadStart").Offset(0, 1), _
.Cells(.Range("WorkloadEnd").Row, _
.Range("WorkloadStart").Column).Offset(0, 2))
End With

With rngCond
wsInput.Visible = xlSheetVisible
'Stop
Debug.Print .Cells(2, 1).Address(False, True)
sForm = "=AND(" & .Cells(2, 1).Address(False, True) & "=TechOpsName, "
& _
.Cells(2, 2).Address(False, True) & "=ContractName)"
Debug.Print sForm

.Cells(2,2).FormatConditions.Add xlExpression, , sForm
'wsInput.Range("C218").FormatConditions.Add xlExpression, , sForm

'Stop
.Cells(2, 1).FormatConditions(2).Interior.ColorIndex = 40

.Cells(2, 1).Copy

.PasteSpecial xlPasteFormats

.Cells(1, 1).Font.Bold = True
.Cells(1, 2).Font.Bold = True
.Cells(1, 1).HorizontalAlignment = xlCenter
.Cells(1, 2).HorizontalAlignment = xlCenter
End With
Next wsInput


--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003