Struggling to make code work!

[sparky3883]

Hey All

Could someone please have a look at this code for me.
It is to go onto a rota that i have created for work. The main part of
the rota is grey. When you enter an employees 'shift' time the times
that they are going to be working are changed from grey to white.
At first i had the coding so that when you press a button the shift
times change to white, but i would prefer for it to happen
automatically as soon as i have entered the shift time. But i can't
seem to get this code right.
I keep getting a compile error saying 'Next without For' ( i hink
that's what it says)

Any help would be much appreciated#

Thanks





Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(TargetColumns(5)) Is Nothing Then
Range("D8:AJ106").Interior.ColorIndex = 15
Cells.ShrinkToFit = True
For Each cell In Range("B8:AJ106")
With cell
Select Case .Text
Case "6~10"
Range("D" & cell.Row). _
Resize(1, 8).Interior.ColorIndex = 0
Case "6~11"
Range("D" & cell.Row). _
Resize(1, 10).Interior.ColorIndex = 0
Case "6~12"
Range("D" & cell.Row). _
Resize(1, 12).Interior.ColorIndex = 0
Case "6~3"
Range("D" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
Case "7~4"
Range("F" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
Case "E"
Range("I" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
Case "8~5"
Range("H" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
Case "8.30~5.30"
Range("I" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
Case "9~6"
Range("J" & cell.Row). _
Resize(1, 18).Interior.ColorIndex = 0
End Select
Next cell
End With

End Sub

 

[pikus]

Your problem is here:

If Not Intersect(TargetColumns(5)) Is Nothing Then
Range("D8:AJ106").Interior.ColorIndex = 15
Cells.ShrinkToFit = True

You need an End If statement somewhere. Where you need it depends o
what you want to be conditional with the IF statement. Probabl
either:

If Not Intersect(TargetColumns(5)) Is Nothing Then
Range("D8:AJ106").Interior.ColorIndex = 15
End If
Cells.ShrinkToFit = True

Or:

If Not Intersect(TargetColumns(5)) Is Nothing Then
Range("D8:AJ106").Interior.ColorIndex = 15
Cells.ShrinkToFit = True
End If

Also your For Each Loop starts before your With statement then end
before the With statement is ended. That will also mess you up. Yo
chould change it from:

Next cell
End With

To:

End With
Next cell

- Piku