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

danbates

Active Member
Joined
Oct 8, 2017
Messages
377
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
    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

VBA Code:
 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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,224,828
Messages
6,181,210
Members
453,023
Latest member
alabaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top