i need help with excel macro for offset?

Question

i need help with offset in excel macro
this is my code,but it dont seem right,i need it to color in cells to the left

Sub Service_Check()
Dim Mycell As Range
For Each Mycell In Range("miles_To_Date")
If Mycell.Value > 6000 Then
With Mycell.Interior
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
Mycell.Offset(0, -8)
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next
End Sub

Answer 1

Your syntax is wrong. Everything between the With/End With statements is applying to the Mycell object. Putting mycell.offset(0.-8) does not make sense to Excel. If you want the color, pattern etc to apply to the cells on the left, change the code to read:

Sub Service_Check()
Dim Mycell As Range
For Each Mycell In Range("miles_To_Date")
If Mycell.Value > 6000 Then
With Mycell.Offset(0,-8).Interior
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next
End Sub

If you want only the color to apply to the cells on the left, add another with statement (still inside your For Each loop) that looks like this:

With Mycell.Offset(0,-8).Interior
.ColorIndex = 4
End With

Hope this helps.

Answer 2

You need two nested with
one before
Mycell.Offset(0, -8)