Forum Discussion

ONG ZHEN YANG RP's avatar
ONG ZHEN YANG RP
Brass Contributor
Apr 28, 2018

Needs Help on the VBA for an Attendance Sheet

Hi! I need to create a new routine so that it will highlight in the "Summary" sheet in i) red for courses that the staff has not attended after the deadline has passed, and ii) yellow for courses ...
Formulas & Functions
VBA
ONG ZHEN YANG RP's avatar
ONG ZHEN YANG RP
Brass Contributor
Apr 30, 2018

Tomasz Kocur ermm... I just realized that there is only one little problem left.. From row 103 and onwards, column C to E is highlighted in red the whole way down

Tomasz Kocur's avatar
Tomasz Kocur
Brass Contributor
Apr 30, 2018
not in my copy
NoNames = .Cells(50000, 1).End(xlUp).Row - 1�

is a calculating number of rows that macro is going through
try highlighting all rows below 103 and remove entire rows

https://drive.google.com/file/d/1j9iel09lSFb3kwa1o-bcu7yQ9rq1TEyw/view?usp=sharing
  • ONG ZHEN YANG RP's avatar
    ONG ZHEN YANG RP
    Brass Contributor
    Apr 30, 2018

    Tomasz Kocur I realized that there are a few mistakes. For example 

    If you look at Row 6, Robyn Pierce had already attended the Customer Service before the deadline based on the attendance Sheet but Customer Service is still highlighted in Red.

    • Tomasz Kocur's avatar
      Tomasz Kocur
      Brass Contributor
      May 01, 2018

      Here become something more...
      You need to have clean data... " Rudy Barnes" in the Attendance list tab has space in front of the name, while in the Summary tab is without space "Rudy Barnes"

       

      There is always a possibility to add another loop that will go through each name and clean data from extra spaces or "non-printable characters" (which are quite often copied from servers reports or users interfaces)

      • ONG ZHEN YANG RP's avatar
        ONG ZHEN YANG RP
        Brass Contributor
        May 01, 2018

        Tomasz Kocur Oh okay! I made sure of that and everything seems okay now :)

  • ONG ZHEN YANG RP's avatar
    ONG ZHEN YANG RP
    Brass Contributor
    Apr 30, 2018

    Tomasz Kocur I found out what went wrong and it works fine now. Thanks for all of your help!! :)