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
--