Hi all i have modified the code below........and indeed it does colour a
cell Red after finding the date in the rng.....however the cell it is
colouring red is the last clicked cell (activecell) on the sheet!, not
only is it colouring it red it is filling it with the contents of "r"
(mycell.offset(x, x)) any ideas why this is happening?
Regards,
Simon
Sub staffdates()
Dim wks As Worksheet
Dim rng As Range
Dim strFirst As String
Dim arr As Variant
Dim t1 As Variant
Dim r As Range
Dim mycell
dv = ComboBox2.Text
sn = ComboBox1.Text
arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", _
"Week6")
'On Error GoTo XIT
Application.EnableEvents = False
For Each wks In Worksheets(arr)
wks.Visible = xlSheetVisible
Set rng = wks.Range("A1:A300")
For Each mycell In rng
If mycell.Text = dv Then
MsgBox "found " & mycell.Text
Sheets("Week Selection").Visible = False
With Worksheets(arr)
Select Case sn
Case Is = "Lauren"
Set r = mycell.Offset(1, 1)
Case Is = "Emma"
Set r = mycell.Offset(1, 2)
Case Is = "Cheryl"
Set r = mycell.Offset(1, 3)
End Select
End With
End If
Selection = r
Selection.Interior.ColorIndex = 3
Selection.Interior.Pattern = xlSolid
Selection.Interior.PatternColorIndex = xlAutomatic
Next mycell
Exit Sub
wks.Visible = xlSheetHidden
Next wks
XIT:
Application.EnableEvents = True
Worksheets("Week Selection").Visible = True
Unload Me
End Sub
Private Sub ComboBox2_Change()
ComboBox2 = Format(ComboBox2.Value, "dd mmmm yyyy")
End Sub
Private Sub CommandButton1_Click()
Call staffdates
End Sub
--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread:
http://www.excelforum.com/showthread...hreadid=557873