VB macro for highlighting rows if conditions are met

[keibeezy]

I am making a schedule for work and need help with a VB macro.
in column A10:A30 i have a person's name, and in the adjacent Column B10:B30 I have their regular days off, represented by a two-letter code.
MT=monday/tuesday
TW=tuesday/wednesday
WT=wednesday/thursday
TF=thursday/friday
FS=friday/saturday
SS=saturday/sunday
SM=sunday/monday
In cell C3, If I type in MONDAY for the day, i want it to highlight each row that has the letter "M" next to their name, and so on for the rest of the days of the week. and if it could return the text "RDO" in the adjacent cell in column C automatically. Ive had no luck searching for a similar macro. help please?

 

[Michael M]

What happens when you type "TUESDAY or THURSDAY" in C3 ??.....It's going to highlight ALL Tuesdays AND Thursdays
Wouldn't you better off using the 2 letter code in C3 ?

 

[keibeezy]

well yeah i suppose the 2letter code would be easier and perfectly acceptable, but what would a macro for that look like? thanks for the reply

 

[Teeroy]

It's quick and could be tidied up but try the following;

Sub highlight_names()
Dim RDO, rng As Range

With Sheets("Sheet1") 'Change to your sheet name
    Select Case .Range("C3").Value
    Case Is = "Monday"
        RDO = Array("SM", "MT")
    Case Is = "Tuesday"
        RDO = Array("MT", "TW")
    Case Is = "Wednesday"
            RDO = Array("TW", "WT")
    Case Is = "Thursday"
        RDO = Array("WT", "TF")
    Case Is = "Friday"
        RDO = Array("TF", "FS")
    Case Is = "Saturday"
        RDO = Array("FS", "SS")
    Case Is = "Sunday"
        RDO = Array("SS", "SM")
    Case Else
        Exit Sub
    End Select
    'clear existing formats and RDO
    .Range("B10:B30").EntireRow.Interior.ColorIndex = 0
    .Range("C10:C30").ClearContents
    'test days off
    For Each rng In Range("B10:B30")
        If Not UBound(Filter(RDO, rng.Value)) Then
            rng.EntireRow.Interior.ColorIndex = 4
            rng.Offset(, 1) = "RDO"
        End If
    Next
End With
End Sub

 

[Michael M]

Why not

Sub MM1()
Dim r As Long
For r = 10 To 30
    If Range("B" & r).Value = Range("C3").Value Then
        Rows(r).Interior.ColorIndex = 8
       Range("C" & r).Value= "RDO"
End If
Next r
End Sub

 

[Teeroy]

The only problem with using the two day code is that each day belongs to two of them, so you can't tell who's available on a Monday without checking both SM and MT.

 

[Michael M]

That's why I suggested the 2 day code in C3 rather than the "Monday"
It's then only got to find the matching code in col "B"
Or, is it past my bedtime !!!

 

[Teeroy]

My reading of the original post is that on a particular day they wanted to know who was not available. But I'm in your time zone Michael (unless you're in BH) so my brain might be past its "Best Before" as well.

 

[Michael M]

I'd rather be in BH...but I'm not quite that far west !!
No, I think it's simply a matter of interpretation.....we'll have to wait for the OP to advise.....

 

[keibeezy]

Yeah I just need to show who is off on a given day. Work wont let me change the format for the 2 letter RDO, but wont provide a form either, so i needed to find a work around. Teeroy's code worked great, but i have to assign it to a hot key to work, and it stays highliighted even if i clear the cells. is there a way for it to auto unhighlight and rehighlight? or am i doing something wrong? i need to try everyones suggestions. thanks all for the replies.