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