Ian,

I was just working on adding some CF in VBA the other day. One thing I
noticed is that the CF formula is relative to the selected range. That's
what you are seeing here. With each iteration you move one cell away from
L2 and so the CF formatting increments the formula. You could select the
cell each time, like this:

Sub test()

Dim x As Long
Dim strRange As String
Dim strCondition1 As String

For x = 2 To 7
strRange = "L" & x
Range(strRange).Select
Debug.Print strRange
strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")"
Debug.Print strCondition1
With Worksheets("Time sheet for submission").Range(strRange)
With .FormatConditions
.Delete
.Add xlExpression, , strCondition1
End With
With .Font
.ColorIndex = 3
End With
End With
Next
End Sub

But by selecting the whole range, running the Macro Recorder and then
tweaking slightly, you get a much better solution:

Sub test2()
With Range("L2:L7")
.Select
With .FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:="=DAY(A2)=DAY(A3)"
End With
.Font.ColorIndex = 3
End With
End Sub

"Select" is generally frowned upon because it slows things down, and I'm
sure there's a way to get around it here, but I think this is okay. Also
notice that you need to delete the conditions if there's any chance that
there was already CF in this range, otherwise you're added CF will be #2 or
#3, or, I presume, a run-time error, in the case of #4.

hth,

Doug

"Ian" wrote in message
...
This code is supposed toset a conditional format to "hide" the contents of
a cell if the following line has the same date:

Sub test()
Dim strRange As String
Dim strCondition1 As String
For x = 2 To 7
strRange = "L" & x
Debug.Print strRange
strCondition1 = "=DAY(A" & x & ")=DAY(A" & x + 1 & ")"
Debug.Print strCondition1
With Worksheets("Time sheet for
submission").Range(strRange).FormatConditions.Add( xlExpression, ,
strCondition1)
With .Font
.ColorIndex = 3
End With
End With
Next
End Sub


I set sthe Debug.Print lines to try to understand where the code is going
wrong, but I still can't figure it out. On the face of it, the code should
work, especially given the output I got in the Immediate window:
L2
=DAY(A2)=DAY(A3)
L3
=DAY(A3)=DAY(A4)
L4
=DAY(A4)=DAY(A5)
L5
=DAY(A5)=DAY(A6)
L6
=DAY(A6)=DAY(A7)
L7
=DAY(A7)=DAY(A8)

This is as expected, but the forumlae in the spreadsheet reference the
wrong lines after the first instance as below.

Conditional format contents:
L2 refers to A2 A3 as expected but
L3 refers to A4 A5 instead of A3 A4
-
-
-
L7 refers to A12 A13

Can anyone suggest a cure for what's happening here?

--
Ian
--