Searching array for date and changing offset colour depeding on combobox content?

Hi all, i have a Userform3 that has 2 comboboxes on it one selects a name the other selects a date, when clicking the command button how do i get to search through arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", _
"Week6") to find the date and when it does offset(1,x) and change colour lets say to Red where x would be dependent on the name in the combobox e.g offset(1,1) would be Lauren offset(1,2) would be Emma and offset(1,3) would be Cheryl, then once they have had their holiday and the colour change is older than one week turn the cell colour back to its original colour (they are different for each girl), The dates appear only in column A in the places listed below:

Range("A1")
Range("A49")
Range("A97")
Range("A145")
Range("A193")

Probably trying to do too much here!, all help much appreciated!

Regards,
Simon

I am not sure what the precise problem is you are trying to solve, but this
will get you the array element that has the particular week:

dim i as long

for i = 0 to ubound(arr)
if arr(i) = "Week3" then
exit for
end if
next

msgbox "Week3 found in array element " & i

RBS

"Simon Lloyd" <Simon.Lloyd.2ac1fb_1151868002.682@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.2ac1fb_1151868002.682@excelforum-nospam.com...
>
> Hi all, i have a Userform3 that has 2 comboboxes on it one selects a
> name the other selects a date, when clicking the command button how do
> i get to search through arr = Array("Week1", "Week2", "Week3", "Week4",
> "Week5", _
> "Week6") to find the date and when it does offset(1,x) and change
> colour lets say to Red where x would be dependent on the name in the
> combobox e.g offset(1,1) would be Lauren offset(1,2) would be Emma and
> offset(1,3) would be Cheryl, then once they have had their holiday and
> the colour change is older than one week turn the cell colour back to
> its original colour (they are different for each girl), The dates
> appear only in column A in the places listed below:
>
> Range("A1")
> Range("A49")
> Range("A97")
> Range("A145")
> Range("A193")
>
> Probably trying to do too much here!, all help much appreciated!
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=557701
>

Thanks for the reply RB i'm not trying to find a week (or sheet named that week) in the array i want to search through the sheets in the array looking for a date match to the one in combobox1 on my userform3 when it finds the match i need it to offset(1,x) and change colour lets say to Red where x would be dependent on the name in the combobox2 e.g offset(1,1) would be Lauren offset(1,2) would be Emma and offset(1,3) would be Cheryl, then once they have had their holiday and the colour change is older than one week turn the cell colour back to its original colour (they are different for each girl), The dates appear only in column A on each of the sheets in the places listed below:

Range("A1")
Range("A49")
Range("A97")
Range("A145")
Range("A193")

Any other ideas?

Regards,
Simon

So, what is the exact bit you are having a problem with?
Maybe have a look at the posting: Row number of a specific value in this
same NG, posted today.

RBS

"Simon Lloyd" <Simon.Lloyd.2ac39z_1151870402.5828@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.2ac39z_1151870402.5828@excelforum-nospam.com...
>
> Thanks for the reply RB i'm not trying to find a week (or sheet named
> that week) in the array i want to search through the sheets in the
> array looking for a date match to the one in combobox1 on my userform3
> when it finds the match i need it to offset(1,x) and change colour lets
> say to Red where x would be dependent on the name in the combobox2 e.g
> offset(1,1) would be Lauren offset(1,2) would be Emma and offset(1,3)
> would be Cheryl, then once they have had their holiday and the colour
> change is older than one week turn the cell colour back to its original
> colour (they are different for each girl), The dates appear only in
> column A on each of the sheets in the places listed below:
>
> Range("A1")
> Range("A49")
> Range("A97")
> Range("A145")
> Range("A193")
>
> Any other ideas?
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=557701
>

RB the posting didn't have anything relevant to what i was trying to achieve, so here is what i have patched together to try to do the job, it does search each worksheet one by one but does not find the value in the combobox....but it does exist, can you help try and straighten this out?

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
dv = ComboBox2.Value
sn = ComboBox1.Value
arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", _
"Week6")
With Worksheets(arr)
Select Case sn
Case Is = "Lauren"
Set r = ActiveCell.Offset(1, 1)
Case Is = "Emma"
Set r = ActiveCell.Offset(1, 2)
Case Is = "Cheryl"
Set r = ActiveCell.Offset(1, 3)
End Select
End With
On Error GoTo XIT
Application.EnableEvents = False


For Each wks In Worksheets(arr)
wks.Visible = xlSheetVisible
Set rng = wks.Cells.Find(What:=dv, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If Not rng Is Nothing Then
strFirst = rng.Address
Do
wks.Select
rng.Select
r.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

If MsgBox("How about this one... " & rng.Text, _
vbYesNo, "Customer Found") = vbYes Then
Exit Sub
End If
Set rng = wks.Cells.FindNext(rng)
Loop Until rng.Address = strFirst
End If
wks.Visible = xlSheetHidden
Next wks
MsgBox PrompT:="Sorry, that was all of them", _
Buttons:=vbInformation, _
Title:="Search Complete"

XIT:
Application.EnableEvents = True

End Sub


Private Sub ComboBox2_Change()
ComboBox2 = Format(ComboBox2.Value, "dd/mmmm/yyyy")
End Sub

Private Sub CommandButton1_Click()
Call staffdates

End Sub

Not sure what is going on here:

rng.Select
r.Select

rng.Select won't do anything here as you are selecting another range
straight after.
Selecting ranges should be avoided in any case as you don't have to and it
will slow your code down.

RBS

"Simon Lloyd" <Simon.Lloyd.2acbdn_1151880902.7793@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.2acbdn_1151880902.7793@excelforum-nospam.com...
>
> RB the posting didn't have anything relevant to what i was trying to
> achieve, so here is what i have patched together to try to do the job,
> it does search each worksheet one by one but does not find the value in
> the combobox....but it does exist, can you help try and straighten this
> out?
>
> 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
> dv = ComboBox2.Value
> sn = ComboBox1.Value
> arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", _
> "Week6")
> With Worksheets(arr)
> Select Case sn
> Case Is = "Lauren"
> Set r = ActiveCell.Offset(1, 1)
> Case Is = "Emma"
> Set r = ActiveCell.Offset(1, 2)
> Case Is = "Cheryl"
> Set r = ActiveCell.Offset(1, 3)
> End Select
> End With
> On Error GoTo XIT
> Application.EnableEvents = False
>
>
> For Each wks In Worksheets(arr)
> wks.Visible = xlSheetVisible
> Set rng = wks.Cells.Find(What:=dv, _
> LookIn:=xlFormulas, _
> LookAt:=xlPart, _
> MatchCase:=False)
> If Not rng Is Nothing Then
> strFirst = rng.Address
> Do
> wks.Select
> rng.Select
> r.Select
> With Selection.Interior
> ColorIndex = 3
> Pattern = xlSolid
> PatternColorIndex = xlAutomatic
> End With
>
> If MsgBox("How about this one... " & rng.Text, _
> vbYesNo, "Customer Found") = vbYes Then
> Exit Sub
> End If
> Set rng = wks.Cells.FindNext(rng)
> Loop Until rng.Address = strFirst
> End If
> wks.Visible = xlSheetHidden
> Next wks
> MsgBox PrompT:="Sorry, that was all of them", _
> Buttons:=vbInformation, _
> Title:="Search Complete"
>
> XIT:
> Application.EnableEvents = True
>
> 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=557701
>