If you want to add the conditional format to C218, then either do it outside
the With rngCond statement or if you do it inside the With statement,
compensate for the relative offset. I think it would be easier to do it
outside the With statement, personally.

"J Streger" wrote:

That is by design. The area I am working with is on a particular section of a
sheet, and I use named ranges to pinpoint the areas to work with. So I want
to with with the cell that is 1 row and 0 columns offset from that cell.
sForm is being filled the formula I want. but I want, in this case, C218 to
have the conditional formula "=AND($C218=TechOpsName, $D218=ContractName)",
which is what sForm is equal to when I run the code. But when I add it to
C218, the rows change to 428.

I have a line commented out where I try to set the Conditional Formatting to
C218 directly just in case my referencing was off, or it was getting confused
by the range.cells referencing, but same result. I don't understand what
function of Excel is causing the string formula I add to the cell to change
rows as if it knows better than I do.





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

User of MS Office 2003



"JLGWhiz" wrote:

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