Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format problem
This code is supposed toset a conditional format to "hide" the contents of a
cell if the following line has the same date: Sub test() Dim strRange As String Dim strCondition1 As String For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub I set sthe Debug.Print lines to try to understand where the code is going wrong, but I still can't figure it out. On the face of it, the code should work, especially given the output I got in the Immediate window: L2 =DAY(A2)=DAY(A3) L3 =DAY(A3)=DAY(A4) L4 =DAY(A4)=DAY(A5) L5 =DAY(A5)=DAY(A6) L6 =DAY(A6)=DAY(A7) L7 =DAY(A7)=DAY(A8) This is as expected, but the forumlae in the spreadsheet reference the wrong lines after the first instance as below. Conditional format contents: L2 refers to A2 A3 as expected but L3 refers to A4 A5 instead of A3 A4 - - - L7 refers to A12 A13 Can anyone suggest a cure for what's happening here? -- Ian -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format problem
Ian,
I was just working on adding some CF in VBA the other day. One thing I noticed is that the CF formula is relative to the selected range. That's what you are seeing here. With each iteration you move one cell away from L2 and so the CF formatting increments the formula. You could select the cell each time, like this: Sub test() Dim x As Long Dim strRange As String Dim strCondition1 As String For x = 2 To 7 strRange = "L" & x Range(strRange).Select Debug.Print strRange strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange) With .FormatConditions .Delete .Add xlExpression, , strCondition1 End With With .Font .ColorIndex = 3 End With End With Next End Sub But by selecting the whole range, running the Macro Recorder and then tweaking slightly, you get a much better solution: Sub test2() With Range("L2:L7") .Select With .FormatConditions .Delete .Add Type:=xlExpression, Formula1:="=DAY(A2)=DAY(A3)" End With .Font.ColorIndex = 3 End With End Sub "Select" is generally frowned upon because it slows things down, and I'm sure there's a way to get around it here, but I think this is okay. Also notice that you need to delete the conditions if there's any chance that there was already CF in this range, otherwise you're added CF will be #2 or #3, or, I presume, a run-time error, in the case of #4. hth, Doug "Ian" wrote in message ... This code is supposed toset a conditional format to "hide" the contents of a cell if the following line has the same date: Sub test() Dim strRange As String Dim strCondition1 As String For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub I set sthe Debug.Print lines to try to understand where the code is going wrong, but I still can't figure it out. On the face of it, the code should work, especially given the output I got in the Immediate window: L2 =DAY(A2)=DAY(A3) L3 =DAY(A3)=DAY(A4) L4 =DAY(A4)=DAY(A5) L5 =DAY(A5)=DAY(A6) L6 =DAY(A6)=DAY(A7) L7 =DAY(A7)=DAY(A8) This is as expected, but the forumlae in the spreadsheet reference the wrong lines after the first instance as below. Conditional format contents: L2 refers to A2 A3 as expected but L3 refers to A4 A5 instead of A3 A4 - - - L7 refers to A12 A13 Can anyone suggest a cure for what's happening here? -- Ian -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format problem
Use RC notation
Sub test() Dim strRange As String Dim strCondition1 As String Dim x For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ian" wrote in message ... This code is supposed toset a conditional format to "hide" the contents of a cell if the following line has the same date: Sub test() Dim strRange As String Dim strCondition1 As String For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub I set sthe Debug.Print lines to try to understand where the code is going wrong, but I still can't figure it out. On the face of it, the code should work, especially given the output I got in the Immediate window: L2 =DAY(A2)=DAY(A3) L3 =DAY(A3)=DAY(A4) L4 =DAY(A4)=DAY(A5) L5 =DAY(A5)=DAY(A6) L6 =DAY(A6)=DAY(A7) L7 =DAY(A7)=DAY(A8) This is as expected, but the forumlae in the spreadsheet reference the wrong lines after the first instance as below. Conditional format contents: L2 refers to A2 A3 as expected but L3 refers to A4 A5 instead of A3 A4 - - - L7 refers to A12 A13 Can anyone suggest a cure for what's happening here? -- Ian -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format problem
Forgot to mention, best to clear any exsiting conditions down, just in case
Sub test() Dim strRange As String Dim strCondition1 As String Dim x, i As Long For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange) For i = 1 To .FormatConditions.Count .FormatConditions(i).Delete Next i With .FormatConditions.Add(xlExpression, , strCondition1) .Font.ColorIndex = 3 End With End With Next End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Use RC notation Sub test() Dim strRange As String Dim strCondition1 As String Dim x For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ian" wrote in message ... This code is supposed toset a conditional format to "hide" the contents of a cell if the following line has the same date: Sub test() Dim strRange As String Dim strCondition1 As String For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub I set sthe Debug.Print lines to try to understand where the code is going wrong, but I still can't figure it out. On the face of it, the code should work, especially given the output I got in the Immediate window: L2 =DAY(A2)=DAY(A3) L3 =DAY(A3)=DAY(A4) L4 =DAY(A4)=DAY(A5) L5 =DAY(A5)=DAY(A6) L6 =DAY(A6)=DAY(A7) L7 =DAY(A7)=DAY(A8) This is as expected, but the forumlae in the spreadsheet reference the wrong lines after the first instance as below. Conditional format contents: L2 refers to A2 A3 as expected but L3 refers to A4 A5 instead of A3 A4 - - - L7 refers to A12 A13 Can anyone suggest a cure for what's happening here? -- Ian -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format problem
Bob,
Is there a reason not to just use: ..FormatConditionsDelete I tried it on a few tests, including ranges with different numbers of FormatConditions, and it worked. But knowing you, there's a good reason for the way you did it. thanks, Doug "Bob Phillips" wrote in message ... Forgot to mention, best to clear any exsiting conditions down, just in case Sub test() Dim strRange As String Dim strCondition1 As String Dim x, i As Long For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange) For i = 1 To .FormatConditions.Count .FormatConditions(i).Delete Next i With .FormatConditions.Add(xlExpression, , strCondition1) .Font.ColorIndex = 3 End With End With Next End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Use RC notation Sub test() Dim strRange As String Dim strCondition1 As String Dim x For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ian" wrote in message ... This code is supposed toset a conditional format to "hide" the contents of a cell if the following line has the same date: Sub test() Dim strRange As String Dim strCondition1 As String For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub I set sthe Debug.Print lines to try to understand where the code is going wrong, but I still can't figure it out. On the face of it, the code should work, especially given the output I got in the Immediate window: L2 =DAY(A2)=DAY(A3) L3 =DAY(A3)=DAY(A4) L4 =DAY(A4)=DAY(A5) L5 =DAY(A5)=DAY(A6) L6 =DAY(A6)=DAY(A7) L7 =DAY(A7)=DAY(A8) This is as expected, but the forumlae in the spreadsheet reference the wrong lines after the first instance as below. Conditional format contents: L2 refers to A2 A3 as expected but L3 refers to A4 A5 instead of A3 A4 - - - L7 refers to A12 A13 Can anyone suggest a cure for what's happening here? -- Ian -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format problem
No good reason Doug, that is a better idea. I was trying it out on 2007, and
I got bogged down thinking about more than 3 conditions. Your suggestion works just as well in 2007 also. Bob "Doug Glancy" wrote in message ... Bob, Is there a reason not to just use: .FormatConditionsDelete I tried it on a few tests, including ranges with different numbers of FormatConditions, and it worked. But knowing you, there's a good reason for the way you did it. thanks, Doug "Bob Phillips" wrote in message ... Forgot to mention, best to clear any exsiting conditions down, just in case Sub test() Dim strRange As String Dim strCondition1 As String Dim x, i As Long For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange) For i = 1 To .FormatConditions.Count .FormatConditions(i).Delete Next i With .FormatConditions.Add(xlExpression, , strCondition1) .Font.ColorIndex = 3 End With End With Next End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Use RC notation Sub test() Dim strRange As String Dim strCondition1 As String Dim x For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ian" wrote in message ... This code is supposed toset a conditional format to "hide" the contents of a cell if the following line has the same date: Sub test() Dim strRange As String Dim strCondition1 As String For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub I set sthe Debug.Print lines to try to understand where the code is going wrong, but I still can't figure it out. On the face of it, the code should work, especially given the output I got in the Immediate window: L2 =DAY(A2)=DAY(A3) L3 =DAY(A3)=DAY(A4) L4 =DAY(A4)=DAY(A5) L5 =DAY(A5)=DAY(A6) L6 =DAY(A6)=DAY(A7) L7 =DAY(A7)=DAY(A8) This is as expected, but the forumlae in the spreadsheet reference the wrong lines after the first instance as below. Conditional format contents: L2 refers to A2 A3 as expected but L3 refers to A4 A5 instead of A3 A4 - - - L7 refers to A12 A13 Can anyone suggest a cure for what's happening here? -- Ian -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format problem
Thanks Bob.
Doug "Bob Phillips" wrote in message ... No good reason Doug, that is a better idea. I was trying it out on 2007, and I got bogged down thinking about more than 3 conditions. Your suggestion works just as well in 2007 also. Bob "Doug Glancy" wrote in message ... Bob, Is there a reason not to just use: .FormatConditionsDelete I tried it on a few tests, including ranges with different numbers of FormatConditions, and it worked. But knowing you, there's a good reason for the way you did it. thanks, Doug "Bob Phillips" wrote in message ... Forgot to mention, best to clear any exsiting conditions down, just in case Sub test() Dim strRange As String Dim strCondition1 As String Dim x, i As Long For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange) For i = 1 To .FormatConditions.Count .FormatConditions(i).Delete Next i With .FormatConditions.Add(xlExpression, , strCondition1) .Font.ColorIndex = 3 End With End With Next End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Use RC notation Sub test() Dim strRange As String Dim strCondition1 As String Dim x For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ian" wrote in message ... This code is supposed toset a conditional format to "hide" the contents of a cell if the following line has the same date: Sub test() Dim strRange As String Dim strCondition1 As String For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub I set sthe Debug.Print lines to try to understand where the code is going wrong, but I still can't figure it out. On the face of it, the code should work, especially given the output I got in the Immediate window: L2 =DAY(A2)=DAY(A3) L3 =DAY(A3)=DAY(A4) L4 =DAY(A4)=DAY(A5) L5 =DAY(A5)=DAY(A6) L6 =DAY(A6)=DAY(A7) L7 =DAY(A7)=DAY(A8) This is as expected, but the forumlae in the spreadsheet reference the wrong lines after the first instance as below. Conditional format contents: L2 refers to A2 A3 as expected but L3 refers to A4 A5 instead of A3 A4 - - - L7 refers to A12 A13 Can anyone suggest a cure for what's happening here? -- Ian -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format problem
Bob & Doug
Thanks for your combined effort. I can get the code to work perfectly as a standalone macro in Excel. Unfortunately I'm trying to use it as part of an Access macro to add formulae and formatting to an Excle sheet. Most of the other code I've used has easily transferred, but this isn't working out. If I use: objExcel.activecell.FormatConditions.Add(xlExpress ion,,strCondition1) I immediately get a VB compile error saying "Expected: =" when trying to leave the line. If I use: objExcel.activecell.FormatConditions.Add Type:= xlExpression, Formula1:= strCondition1 the code appears to compile correctly, but when I run it I get "Run-time error '5' Invalid procedure call or argument" I'm using strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" and I've also tried "=DAY(A2)=DAY(A3)" The earlier code produces the spreadsheet so there are no pre-existing conditions applied, but I've added the line objExcel.activecell.FormatConditions.Delete before the line above and this runs fine. Any ideas? -- Ian -- "Bob Phillips" wrote in message ... No good reason Doug, that is a better idea. I was trying it out on 2007, and I got bogged down thinking about more than 3 conditions. Your suggestion works just as well in 2007 also. Bob "Doug Glancy" wrote in message ... Bob, Is there a reason not to just use: .FormatConditionsDelete I tried it on a few tests, including ranges with different numbers of FormatConditions, and it worked. But knowing you, there's a good reason for the way you did it. thanks, Doug "Bob Phillips" wrote in message ... Forgot to mention, best to clear any exsiting conditions down, just in case Sub test() Dim strRange As String Dim strCondition1 As String Dim x, i As Long For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange) For i = 1 To .FormatConditions.Count .FormatConditions(i).Delete Next i With .FormatConditions.Add(xlExpression, , strCondition1) .Font.ColorIndex = 3 End With End With Next End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Use RC notation Sub test() Dim strRange As String Dim strCondition1 As String Dim x For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(RC1)=DAY(R[+1]C1)" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ian" wrote in message ... This code is supposed toset a conditional format to "hide" the contents of a cell if the following line has the same date: Sub test() Dim strRange As String Dim strCondition1 As String For x = 2 To 7 strRange = "L" & x Debug.Print strRange strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")" Debug.Print strCondition1 With Worksheets("Time sheet for submission").Range(strRange).FormatConditions.Add( xlExpression, , strCondition1) With .Font .ColorIndex = 3 End With End With Next End Sub I set sthe Debug.Print lines to try to understand where the code is going wrong, but I still can't figure it out. On the face of it, the code should work, especially given the output I got in the Immediate window: L2 =DAY(A2)=DAY(A3) L3 =DAY(A3)=DAY(A4) L4 =DAY(A4)=DAY(A5) L5 =DAY(A5)=DAY(A6) L6 =DAY(A6)=DAY(A7) L7 =DAY(A7)=DAY(A8) This is as expected, but the forumlae in the spreadsheet reference the wrong lines after the first instance as below. Conditional format contents: L2 refers to A2 A3 as expected but L3 refers to A4 A5 instead of A3 A4 - - - L7 refers to A12 A13 Can anyone suggest a cure for what's happening here? -- Ian -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format Problem | Excel Discussion (Misc queries) | |||
Conditional Format Problem | Excel Discussion (Misc queries) | |||
Conditional Format Problem. | Excel Discussion (Misc queries) | |||
Conditional format problem | Excel Discussion (Misc queries) | |||
help please with conditional format problem | Excel Worksheet Functions |