Conditional Formatting Adding Wrong Formula

[J Streger]

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

 

[JLGWhiz]

I am not sure what this is supposed to do, but right now, it does notheing.
What did you want it to do?

Set rngCond = .Range(.Range("A1:A20").Offset(0, 1), _
.Cells(.Range("B1:B20").Row, _
.Range("A1:A20").Column).Offset(0, 2))

 

[JLGWhiz]

Disregard, that was the modified version for testing.

 

[JLGWhiz]

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]

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]

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]

So even though I am adding the conditional format as a string, with the rows
hardcoded into the formula, because of the relative offset I'm using in the
range object, it is changing the row to reflect that? Wouldn't the commented
line where I set a cell's conditional format directly, without using any
relative references get past that?

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

User of MS Office 2003

 

[JLGWhiz]

I guess I am confused. The formula was the original problem stated and I was
looking at the actual code posted. It is assigning the formula as written in
the code.

Your With statement uses a variable that is a range. I don't know what it
works out to in your code, but I arbitrarily used A1 and B1 for the named
ranges. It then gave me B2 and C2 in the sForm variable and it put the
expression for FormatConditions in C2 all relative to the RngCond address
using the Cells() referencing method. When I removed the apostrophe and
used the Range"C218").FormatConditions.Add xlExpression, , sForm, it put
"=And(B219=TechOpsName, C219=ContractName" in C218 conditional format. That
is the correct entry based on the fact that the variable value was created
relative to rngCond.Address.

If you step through the code and look at each address as you execute the
code, maybe you can find where you need to make changes to produce what you
want.
It took me a couple of tries to get the color to take by changing it to the
same cell reference as the add statement. I am still not sure why you are
using a With statement to the Range variable in the fashion that you have it.
But, if it gives the results you want, then go for it.

 

[J Streger]

Well I removed that with statement (I was just using the range.cells as an
alternative means of using offset. Except the cells ensures I stay within the
range I want to). here is what I came up with:

WorkloadStart's address is B217

With wsInput
sForm = "=AND(" & .Range("WorkloadStart").Offset(1, 1).Address(False,
True) & "=TechOpsName, " & _
.Range("WorkloadStart").Offset(1, 2).Address(False, True) &
"=ContractName)"

.Range("WorkloadStart").Offset(1, 1).FormatConditions.Add
Type:=xlExpression, _
Formula1:=sForm

.Range("WorkloadStart").Offset(1,
1).FormatConditions(2).Interior.ColorIndex = 40

End With

This still results in "=AND($C428=TechOpsName, $D428=ContractName)" being
the formula, not "=AND($C218=TechOpsName, $D218=ContractName)"

I don't understand why when I go into the gui, I can set the format
condition to point to row 218, but when I try to set the same cell with the
same string in the code, the row number changes to 428.

Thank you for all the time you've put into helping me!!

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

User of MS Office 2003

 

[J Streger]

I figured it out. It seems that when you add a formula to a conditional
format, it will change the row references based on the difference from the
currently selected cell. So to ensure my rows don't change, I need to select
the cell I want to add the formats to, so that the reference is considered
0,0 as it were.

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

User of MS Office 2003