Conditional format problem

[Ian]

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?

 

[Doug Glancy]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

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)

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?

 

[Doug Glancy]

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

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)

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)

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?

 

[Bob Phillips]

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

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

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)

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)



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?

 

[Doug Glancy]

Thanks Bob.

Doug

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

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

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)



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)



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]

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(xlExpression,,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
--

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

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

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)



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)



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?

 

[Bob Phillips]

Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you originally
supplied, I created this little test in Word, and it worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

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(xlExpression,,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
--

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

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

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)



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)



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]

Hi Bob

I already had some code modifying cells using activecell, so I modded your
suggested code to suit (to try to keep some consistency). I've since figured
out what the problem was. I hadn't added the line Const xlExpression As Long
= 2.

Stepping thorugh the code I can see everythign working as it should, but
when I save the file using objExcel.ActiveWorkbook.Save, the conditional
formatting is lost. I'm assuming this is because Excel 2000 outputs to an
Excel 5.0/95 format spreadsheet. Do you know if there's a way to force the
saving in Excel 2000 format?

--
Ian
--

Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you originally
supplied, I created this little test in Word, and it worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

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(xlExpression,,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
--

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



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

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)



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)



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]

Oops, just noticed the mistake in my last post. It's ACCESS that outputs to
an Excel 5.0/95 format file, NOT Excel 2000.

Anyone know how to save in a later Excel format from Access?

--
Ian
--

Hi Bob

I already had some code modifying cells using activecell, so I modded your
suggested code to suit (to try to keep some consistency). I've since
figured out what the problem was. I hadn't added the line Const
xlExpression As Long = 2.

Stepping thorugh the code I can see everythign working as it should, but
when I save the file using objExcel.ActiveWorkbook.Save, the conditional
formatting is lost. I'm assuming this is because Excel 2000 outputs to an
Excel 5.0/95 format spreadsheet. Do you know if there's a way to force the
saving in Excel 2000 format?

--
Ian
--

Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you originally
supplied, I created this little test in Word, and it worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

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(xlExpression,,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
--
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



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

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)



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)



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?

 

[Bob Phillips]

Can you try setting the fileformat property of the save to 56?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Oops, just noticed the mistake in my last post. It's ACCESS that outputs
to an Excel 5.0/95 format file, NOT Excel 2000.

Anyone know how to save in a later Excel format from Access?

--
Ian
--

Hi Bob

I already had some code modifying cells using activecell, so I modded
your suggested code to suit (to try to keep some consistency). I've since
figured out what the problem was. I hadn't added the line Const
xlExpression As Long = 2.

Stepping thorugh the code I can see everythign working as it should, but
when I save the file using objExcel.ActiveWorkbook.Save, the conditional
formatting is lost. I'm assuming this is because Excel 2000 outputs to an
Excel 5.0/95 format spreadsheet. Do you know if there's a way to force
the saving in Excel 2000 format?

--
Ian
--

Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you originally
supplied, I created this little test in Word, and it worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



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(xlExpression,,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
--
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



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

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)



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)



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]

Hi Bob

I have absolutely no idea! Any clues as to how I can do this? I've searched
the Access VBA help for anything relating to file format but can't find
anything relevant.

--
Ian
--

Can you try setting the fileformat property of the save to 56?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Oops, just noticed the mistake in my last post. It's ACCESS that outputs
to an Excel 5.0/95 format file, NOT Excel 2000.

Anyone know how to save in a later Excel format from Access?

--
Ian
--

Hi Bob

I already had some code modifying cells using activecell, so I modded
your suggested code to suit (to try to keep some consistency). I've
since figured out what the problem was. I hadn't added the line Const
xlExpression As Long = 2.

Stepping thorugh the code I can see everythign working as it should, but
when I save the file using objExcel.ActiveWorkbook.Save, the conditional
formatting is lost. I'm assuming this is because Excel 2000 outputs to
an Excel 5.0/95 format spreadsheet. Do you know if there's a way to
force the saving in Excel 2000 format?

--
Ian
--
Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you originally
supplied, I created this little test in Word, and it worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



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(xlExpression,,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
--
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



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

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)



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)



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]

Hi Bob

I've been doing some more checking and found references to fileformat in the
MS knowledge base. Unfortunately it's expecting a string, not a number.

--
Ian
--

Can you try setting the fileformat property of the save to 56?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Oops, just noticed the mistake in my last post. It's ACCESS that outputs
to an Excel 5.0/95 format file, NOT Excel 2000.

Anyone know how to save in a later Excel format from Access?

--
Ian
--

Hi Bob

I already had some code modifying cells using activecell, so I modded
your suggested code to suit (to try to keep some consistency). I've
since figured out what the problem was. I hadn't added the line Const
xlExpression As Long = 2.

Stepping thorugh the code I can see everythign working as it should, but
when I save the file using objExcel.ActiveWorkbook.Save, the conditional
formatting is lost. I'm assuming this is because Excel 2000 outputs to
an Excel 5.0/95 format spreadsheet. Do you know if there's a way to
force the saving in Excel 2000 format?

--
Ian
--
Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you originally
supplied, I created this little test in Word, and it worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



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(xlExpression,,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
--
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



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

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)



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)



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?

 

[Bob Phillips]

It's a Long!

I don't work much with Access, but I just ran it up and used this code in
Access

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object

Set oXL = GetObject(, "Excel.Application")
Set oWB = oXL.workbooks.Add
Set oWS = oWB.worksheets(1)

With oWS.range("A1")
With .FormatConditions.Add(2, , "=A1>5")
.interior.colorindex = 3
End With
.Value = 25
End With

oWB.saveas filename:="C:\Test.xls", FileFormat:=46

Set oWS = Nothing
Set oWB = Nothing
Set oXL = Nothing

End Sub

and it created a file fine with CF preserved.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi Bob

I've been doing some more checking and found references to fileformat in
the MS knowledge base. Unfortunately it's expecting a string, not a
number.

--
Ian
--

Can you try setting the fileformat property of the save to 56?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Oops, just noticed the mistake in my last post. It's ACCESS that outputs
to an Excel 5.0/95 format file, NOT Excel 2000.

Anyone know how to save in a later Excel format from Access?

--
Ian
--
Hi Bob

I already had some code modifying cells using activecell, so I modded
your suggested code to suit (to try to keep some consistency). I've
since figured out what the problem was. I hadn't added the line Const
xlExpression As Long = 2.

Stepping thorugh the code I can see everythign working as it should,
but when I save the file using objExcel.ActiveWorkbook.Save, the
conditional formatting is lost. I'm assuming this is because Excel 2000
outputs to an Excel 5.0/95 format spreadsheet. Do you know if there's a
way to force the saving in Excel 2000 format?

--
Ian
--
Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you originally
supplied, I created this little test in Word, and it worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



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(xlExpression,,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
--
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



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

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)



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)



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]

Sorry to be a pain, Bob. I realise Access isn't your thing, but I can't get
your code to work.

I created a new module, pasted your code into it and ran it. The Set oXL
line returns "Run-time error '429': ActiveX component can't create object".

I updated the references to MS ActiveX Data Objects 2.8 Library (from 2.1)
and got over this hurdle, but the "saveas" line returns "Run-time error
'1004': SaveAs method of Workbook class failed". I tried adding references
to MS ActiveX Data Objects 2.8 Recordset Library and MS ActiveX Plugin, but
they didn't help.

Any ideas why this could be? Do I have the wrong version of ActiveX? Do I
need to enable something else in Access?

--
Ian
--

It's a Long!

I don't work much with Access, but I just ran it up and used this code in
Access

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object

Set oXL = GetObject(, "Excel.Application")
Set oWB = oXL.workbooks.Add
Set oWS = oWB.worksheets(1)

With oWS.range("A1")
With .FormatConditions.Add(2, , "=A1>5")
.interior.colorindex = 3
End With
.Value = 25
End With

oWB.saveas filename:="C:\Test.xls", FileFormat:=46

Set oWS = Nothing
Set oWB = Nothing
Set oXL = Nothing

End Sub

and it created a file fine with CF preserved.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi Bob

I've been doing some more checking and found references to fileformat in
the MS knowledge base. Unfortunately it's expecting a string, not a
number.

--
Ian
--

Can you try setting the fileformat property of the save to 56?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Oops, just noticed the mistake in my last post. It's ACCESS that
outputs to an Excel 5.0/95 format file, NOT Excel 2000.

Anyone know how to save in a later Excel format from Access?

--
Ian
--
Hi Bob

I already had some code modifying cells using activecell, so I modded
your suggested code to suit (to try to keep some consistency). I've
since figured out what the problem was. I hadn't added the line Const
xlExpression As Long = 2.

Stepping thorugh the code I can see everythign working as it should,
but when I save the file using objExcel.ActiveWorkbook.Save, the
conditional formatting is lost. I'm assuming this is because Excel
2000 outputs to an Excel 5.0/95 format spreadsheet. Do you know if
there's a way to force the saving in Excel 2000 format?

--
Ian
--
Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you originally
supplied, I created this little test in Word, and it worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



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(xlExpression,,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
--
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



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

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)



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)



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?

 

[Bob Phillips]

Ian,

I don't know what the ActiveX Data Objects has to do with getting an
instance of Excel. It is more likely that Excel was not started, in which
case you should use

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
On Error Goto 0
If oXL Is Nothing Then
Set oXL = CreateObject("Excel.Application")
oXL.Visible = true
End If


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Sorry to be a pain, Bob. I realise Access isn't your thing, but I can't
get your code to work.

I created a new module, pasted your code into it and ran it. The Set oXL
line returns "Run-time error '429': ActiveX component can't create
object".

I updated the references to MS ActiveX Data Objects 2.8 Library (from 2.1)
and got over this hurdle, but the "saveas" line returns "Run-time error
'1004': SaveAs method of Workbook class failed". I tried adding references
to MS ActiveX Data Objects 2.8 Recordset Library and MS ActiveX Plugin,
but they didn't help.

Any ideas why this could be? Do I have the wrong version of ActiveX? Do I
need to enable something else in Access?

--
Ian
--

It's a Long!

I don't work much with Access, but I just ran it up and used this code in
Access

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object

Set oXL = GetObject(, "Excel.Application")
Set oWB = oXL.workbooks.Add
Set oWS = oWB.worksheets(1)

With oWS.range("A1")
With .FormatConditions.Add(2, , "=A1>5")
.interior.colorindex = 3
End With
.Value = 25
End With

oWB.saveas filename:="C:\Test.xls", FileFormat:=46

Set oWS = Nothing
Set oWB = Nothing
Set oXL = Nothing

End Sub

and it created a file fine with CF preserved.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi Bob

I've been doing some more checking and found references to fileformat in
the MS knowledge base. Unfortunately it's expecting a string, not a
number.

--
Ian
--
Can you try setting the fileformat property of the save to 56?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Oops, just noticed the mistake in my last post. It's ACCESS that
outputs to an Excel 5.0/95 format file, NOT Excel 2000.

Anyone know how to save in a later Excel format from Access?

--
Ian
--
Hi Bob

I already had some code modifying cells using activecell, so I modded
your suggested code to suit (to try to keep some consistency). I've
since figured out what the problem was. I hadn't added the line Const
xlExpression As Long = 2.

Stepping thorugh the code I can see everythign working as it should,
but when I save the file using objExcel.ActiveWorkbook.Save, the
conditional formatting is lost. I'm assuming this is because Excel
2000 outputs to an Excel 5.0/95 format spreadsheet. Do you know if
there's a way to force the saving in Excel 2000 format?

--
Ian
--
Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you originally
supplied, I created this little test in Word, and it worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)



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(xlExpression,,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
--
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



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

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)



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)



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]

Hi Bob

I've managed to sort it out. I've modified my original code from
.save
to
.saveas filename:=name fileformat:=xlWorkbookNormal

The reason I couldn't find any reference to fileformat is because I was
looking in Access VBA help, not Excel VBA help. I still don't see where you
got the 46 & 56 from, though.

The only problem I've found is that I had to save to a different filename
that the one I was working on as Excel brings up a dialog warning that the
file already exists etc. and I need it to be transparent to the user. It's
not a problem as I'm outputting data from Access to an Excel sheet,
modifying the sheet, sending the result as a file attachment then deleting
the file. I just have 2 files to delete instead of 1.

--
Ian
--

Ian,

I don't know what the ActiveX Data Objects has to do with getting an
instance of Excel. It is more likely that Excel was not started, in which
case you should use

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
On Error Goto 0
If oXL Is Nothing Then
Set oXL = CreateObject("Excel.Application")
oXL.Visible = true
End If


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Sorry to be a pain, Bob. I realise Access isn't your thing, but I can't
get your code to work.

I created a new module, pasted your code into it and ran it. The Set oXL
line returns "Run-time error '429': ActiveX component can't create
object".

I updated the references to MS ActiveX Data Objects 2.8 Library (from
2.1) and got over this hurdle, but the "saveas" line returns "Run-time
error '1004': SaveAs method of Workbook class failed". I tried adding
references to MS ActiveX Data Objects 2.8 Recordset Library and MS
ActiveX Plugin, but they didn't help.

Any ideas why this could be? Do I have the wrong version of ActiveX? Do I
need to enable something else in Access?

--
Ian
--

It's a Long!

I don't work much with Access, but I just ran it up and used this code
in Access

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object

Set oXL = GetObject(, "Excel.Application")
Set oWB = oXL.workbooks.Add
Set oWS = oWB.worksheets(1)

With oWS.range("A1")
With .FormatConditions.Add(2, , "=A1>5")
.interior.colorindex = 3
End With
.Value = 25
End With

oWB.saveas filename:="C:\Test.xls", FileFormat:=46

Set oWS = Nothing
Set oWB = Nothing
Set oXL = Nothing

End Sub

and it created a file fine with CF preserved.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi Bob

I've been doing some more checking and found references to fileformat
in the MS knowledge base. Unfortunately it's expecting a string, not a
number.

--
Ian
--
Can you try setting the fileformat property of the save to 56?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Oops, just noticed the mistake in my last post. It's ACCESS that
outputs to an Excel 5.0/95 format file, NOT Excel 2000.

Anyone know how to save in a later Excel format from Access?

--
Ian
--
Hi Bob

I already had some code modifying cells using activecell, so I
modded your suggested code to suit (to try to keep some
consistency). I've since figured out what the problem was. I hadn't
added the line Const xlExpression As Long = 2.

Stepping thorugh the code I can see everythign working as it should,
but when I save the file using objExcel.ActiveWorkbook.Save, the
conditional formatting is lost. I'm assuming this is because Excel
2000 outputs to an Excel 5.0/95 format spreadsheet. Do you know if
there's a way to force the saving in Excel 2000 format?

--
Ian
--
Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you originally
supplied, I created this little test in Word, and it worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)



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(xlExpression,,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
--
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



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

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)



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)



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?

 

[Bob Phillips]

You can avoid that message with

oXL.DisplayAlerts = false
.Save
oXL.DisplaAlerts = True

56 was just a value of one of the fileformat constants.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi Bob

I've managed to sort it out. I've modified my original code from
.save
to
.saveas filename:=name fileformat:=xlWorkbookNormal

The reason I couldn't find any reference to fileformat is because I was
looking in Access VBA help, not Excel VBA help. I still don't see where
you got the 46 & 56 from, though.

The only problem I've found is that I had to save to a different filename
that the one I was working on as Excel brings up a dialog warning that the
file already exists etc. and I need it to be transparent to the user. It's
not a problem as I'm outputting data from Access to an Excel sheet,
modifying the sheet, sending the result as a file attachment then deleting
the file. I just have 2 files to delete instead of 1.

--
Ian
--

Ian,

I don't know what the ActiveX Data Objects has to do with getting an
instance of Excel. It is more likely that Excel was not started, in which
case you should use

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
On Error Goto 0
If oXL Is Nothing Then
Set oXL = CreateObject("Excel.Application")
oXL.Visible = true
End If


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Sorry to be a pain, Bob. I realise Access isn't your thing, but I can't
get your code to work.

I created a new module, pasted your code into it and ran it. The Set oXL
line returns "Run-time error '429': ActiveX component can't create
object".

I updated the references to MS ActiveX Data Objects 2.8 Library (from
2.1) and got over this hurdle, but the "saveas" line returns "Run-time
error '1004': SaveAs method of Workbook class failed". I tried adding
references to MS ActiveX Data Objects 2.8 Recordset Library and MS
ActiveX Plugin, but they didn't help.

Any ideas why this could be? Do I have the wrong version of ActiveX? Do
I need to enable something else in Access?

--
Ian
--
It's a Long!

I don't work much with Access, but I just ran it up and used this code
in Access

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object

Set oXL = GetObject(, "Excel.Application")
Set oWB = oXL.workbooks.Add
Set oWS = oWB.worksheets(1)

With oWS.range("A1")
With .FormatConditions.Add(2, , "=A1>5")
.interior.colorindex = 3
End With
.Value = 25
End With

oWB.saveas filename:="C:\Test.xls", FileFormat:=46

Set oWS = Nothing
Set oWB = Nothing
Set oXL = Nothing

End Sub

and it created a file fine with CF preserved.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi Bob

I've been doing some more checking and found references to fileformat
in the MS knowledge base. Unfortunately it's expecting a string, not a
number.

--
Ian
--
Can you try setting the fileformat property of the save to 56?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Oops, just noticed the mistake in my last post. It's ACCESS that
outputs to an Excel 5.0/95 format file, NOT Excel 2000.

Anyone know how to save in a later Excel format from Access?

--
Ian
--
Hi Bob

I already had some code modifying cells using activecell, so I
modded your suggested code to suit (to try to keep some
consistency). I've since figured out what the problem was. I hadn't
added the line Const xlExpression As Long = 2.

Stepping thorugh the code I can see everythign working as it
should, but when I save the file using
objExcel.ActiveWorkbook.Save, the conditional formatting is lost.
I'm assuming this is because Excel 2000 outputs to an Excel 5.0/95
format spreadsheet. Do you know if there's a way to force the
saving in Excel 2000 format?

--
Ian
--
Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you
originally supplied, I created this little test in Word, and it
worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)



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(xlExpression,,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
--
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



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

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)



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)



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]

Hi Bob

Thanks for pointing that out. In the meantime I've written all my code to
use a second file and I've since realised that, in any case, the changed
filename suits my purposes as I can create a unique filename based on user
and date (it's for timesheet and expenses submissions etc).

I'll certainly make use of it if the need ever arises (as it probably will
fairly soon).

--
Ian
--

You can avoid that message with

oXL.DisplayAlerts = false
.Save
oXL.DisplaAlerts = True

56 was just a value of one of the fileformat constants.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hi Bob

I've managed to sort it out. I've modified my original code from
.save
to
.saveas filename:=name fileformat:=xlWorkbookNormal

The reason I couldn't find any reference to fileformat is because I was
looking in Access VBA help, not Excel VBA help. I still don't see where
you got the 46 & 56 from, though.

The only problem I've found is that I had to save to a different filename
that the one I was working on as Excel brings up a dialog warning that
the file already exists etc. and I need it to be transparent to the user.
It's not a problem as I'm outputting data from Access to an Excel sheet,
modifying the sheet, sending the result as a file attachment then
deleting the file. I just have 2 files to delete instead of 1.

--
Ian
--

Ian,

I don't know what the ActiveX Data Objects has to do with getting an
instance of Excel. It is more likely that Excel was not started, in
which case you should use

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
On Error Goto 0
If oXL Is Nothing Then
Set oXL = CreateObject("Excel.Application")
oXL.Visible = true
End If


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Sorry to be a pain, Bob. I realise Access isn't your thing, but I can't
get your code to work.

I created a new module, pasted your code into it and ran it. The Set
oXL line returns "Run-time error '429': ActiveX component can't create
object".

I updated the references to MS ActiveX Data Objects 2.8 Library (from
2.1) and got over this hurdle, but the "saveas" line returns "Run-time
error '1004': SaveAs method of Workbook class failed". I tried adding
references to MS ActiveX Data Objects 2.8 Recordset Library and MS
ActiveX Plugin, but they didn't help.

Any ideas why this could be? Do I have the wrong version of ActiveX? Do
I need to enable something else in Access?

--
Ian
--
It's a Long!

I don't work much with Access, but I just ran it up and used this code
in Access

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object

Set oXL = GetObject(, "Excel.Application")
Set oWB = oXL.workbooks.Add
Set oWS = oWB.worksheets(1)

With oWS.range("A1")
With .FormatConditions.Add(2, , "=A1>5")
.interior.colorindex = 3
End With
.Value = 25
End With

oWB.saveas filename:="C:\Test.xls", FileFormat:=46

Set oWS = Nothing
Set oWB = Nothing
Set oXL = Nothing

End Sub

and it created a file fine with CF preserved.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



Hi Bob

I've been doing some more checking and found references to fileformat
in the MS knowledge base. Unfortunately it's expecting a string, not
a number.

--
Ian
--
Can you try setting the fileformat property of the save to 56?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)



Oops, just noticed the mistake in my last post. It's ACCESS that
outputs to an Excel 5.0/95 format file, NOT Excel 2000.

Anyone know how to save in a later Excel format from Access?

--
Ian
--
Hi Bob

I already had some code modifying cells using activecell, so I
modded your suggested code to suit (to try to keep some
consistency). I've since figured out what the problem was. I
hadn't added the line Const xlExpression As Long = 2.

Stepping thorugh the code I can see everythign working as it
should, but when I save the file using
objExcel.ActiveWorkbook.Save, the conditional formatting is lost.
I'm assuming this is because Excel 2000 outputs to an Excel 5.0/95
format spreadsheet. Do you know if there's a way to force the
saving in Excel 2000 format?

--
Ian
--
Two thoughts spring to mind.

Why are you suddenly using Activecell?Using the code you
originally supplied, I created this little test in Word, and it
worked fine

Sub test()
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object
Const xlExpression As Long = 2

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.workbooks.Open("c:\Testfolders\Some book 1.xls")

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

Set oWS = Nothing
Set oWS = Nothing
oXL.Quit
Set oXL = Nothing

End Sub


Second, are you late binding or early binding?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail
in my addy)



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(xlExpression,,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
--
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



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

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)



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)



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?