Choosing offset on condition found in combobox change colour of cell??

[Simon Lloyd]

I have the code below, it's kind of patched together, it works as far as
finding the date in one of the worksheets in the array (the message box
proves this) but then after finding the date it should choose an offset
depending on the value in combobox1 and then colour it red however this
is where the problem is..........i also want to be able to change the
cell back to its original colour after the date in combobox2 searched
has passed by 7 days.

Any ideas?
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

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
Selection = mycell
With Sheets(wks.Name)
Selection.Interior
ColorIndex = 3
Pattern = xlSolid
PatternColorIndex = xlAutomatic
End With
End If
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]

This post is on from a previous post
http://www.excelforum.com/showthread.php?t=557701, i have tried various
combinations after the Case element but i cannot seem to select or use
"r" and if i dont the code below stops at the With Sheets(wks.Name).

Any ideas?

Regards
Simon

 

[Simon Lloyd]

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