VBA to find last entry, check col C then fills out userform labels accordingly.

[danbates]

Hi,

I have a code already that can find the last entry in col BE and then it fills out the appropriate labels within my userform when loaded.

I would like the following code to work on the last entry if col C has and A

    Dim myRGB_Red As Long:      myRGB_Red = RGB(255, 0, 0)
    Dim myRGB_Blue As Long:     myRGB_Blue = RGB(0, 112, 192)
    Dim myRGB_Green As Long:    myRGB_Green = RGB(0, 176, 80)
    Dim myRGB_Yellow As Long:   myRGB_Yellow = RGB(255, 255, 0)

    Dim lrBE As Long
    Dim vCol

lrBE = Sheets("WEEKLY").Range("BE" & Rows.Count).End(xlUp).Row

 Select Case Sheets("WEEKLY").Range("BE" & Rows.Count).End(xlUp).Value

    Case Is = "DB", "AL", "PD", "AP", "MG", "RT":           vCol = myRGB_Red
    Case Is = "GC", "PETE P", "RW", "DP1":                  vCol = myRGB_Blue
    Case Is = "RH", "MC1", "MN", "PP", "MK1", "RL":         vCol = myRGB_Green
    Case Is = "AS", "MT", "AM3", "HO", "KA", "BL", "LP":    vCol = myRGB_Yellow

 End Select

 With Sheets("WEEKLY").Range("BE" & Rows.Count).End(xlUp)
 LabelInitialsWA.Caption = .Value
 LabelInitialsWA.BackColor = vCol
 End With

UserForm2.LabelDateWA.Caption = Sheets("WEEKLY").cells(lrBE, "BF").Text
UserForm2.LabelTimeWA.Caption = Sheets("WEEKLY").cells(lrBE, "BG").Text

and if col C has a B then it fills out the following

 With Sheets("WEEKLY").Range("BE" & Rows.Count).End(xlUp)
 LabelInitialsWB.Caption = .Value
 LabelInitialsWB.BackColor = vCol
 End With

UserForm2.LabelDateWB.Caption = Sheets("WEEKLY").cells(lrBE, "BF").Text
UserForm2.LabelTimeWB.Caption = Sheets("WEEKLY").cells(lrBE, "BG").Text

Any help would be appreciated.

Thanks

Dan