Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Adding Wrong Formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Adding Wrong Formula
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)) "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Adding Wrong Formula
Disregard, that was the modified version for testing.
"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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Adding Wrong Formula
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Adding Wrong Formula
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Adding Wrong Formula
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Adding Wrong Formula
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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Adding Wrong Formula
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" wrote: 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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Adding Wrong Formula
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 "JLGWhiz" wrote: 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" wrote: 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" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Adding Wrong Formula
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 "JLGWhiz" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help adding a formula to to cell with conditional formatting | Excel Discussion (Misc queries) | |||
Why is my conditional formatting highlighting the wrong dates? | Excel Discussion (Misc queries) | |||
Conditional Formatting: Adding more then 3 | Excel Worksheet Functions | |||
Conditional Formatting - adding condition on formula | Excel Discussion (Misc queries) | |||
Conditional Formula w/Date=Where did I go wrong? | Excel Worksheet Functions |