• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Needs Help on the VBA for an Attendance Sheet

carts

New Member
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 that the staff has attended but only after the deadline has passed.

This has to be based on the "Attendance List" sheet

Attached is the excel file.

Any help is much appreciated!! :)
 

Attachments

  • Staff_list (1).xlsm
    53.7 KB · Views: 5
Hi carts,

It is possible with simple conditional formatting as well.
Take a look in the attached file to see if that's okay for you.
 

Attachments

  • Copy of Staff_list (1).xlsm
    43.3 KB · Views: 5
Hi! @GraH - Guido Thank you so much!

I actually need a sample macro for other purposes as well. Perhaps you can record a macro while you perform all of the conditional formatting steps? :)
 
Hi @GraH - Guido
For some reason, I am unable to achieve the same results when I copy the Conditional Formatting in into the original sheet.. Perhaps you can record a Macro when you are doing it and I would be able to compare and contrast the differences from my own:)
 
Hi !

As if it can't work manually so it can't work neither by code !
For some reason I can't give a try without the original worksheet.
 
Hi, turns out you can't record CF steps.
Sorry, you'll need to wait for someone with more advanced macro writing skills.

Don't understand why copy/paste of the CF-formulae won't work. Try to start from the cell C2 and check if the used ranges match with your original data.
 
@Marc L
Hi! I have attached the original file :)

Anyway can you help me solve another problem too? If you look at the "Capture", you will notice that my typing is configured to this "Black Box" and when I type, it replaces the codes in front of it instead of typing in between. Do you know how I can change it back to the original setting?
 

Attachments

  • Capture.JPG
    Capture.JPG
    58.8 KB · Views: 6
  • Staff_list (2).xlsm
    53.7 KB · Views: 5
If I'm not misreading, Marc is giving it a go. Be patient, in weekends things get a bit slower...
I do have some names in mind, but I personally don't like being pushy towards people I haven't "talked" to a lot, nor offend others by not not tagging. Perhaps @vletm would be kind enough to take a look?
 
eg Abel Silva : what colors do You would like to see is Summary (if needed)
or ANY NAME which would be marked?
Screen Shot 2018-04-28 at 16.07.25.png
 
@vletm For example, Abel Silva, since he has attended all courses that he needs to attend it would be left white in color.

Other than that..
i) red for courses that the staff has not attended after the deadline has passed, and
ii) yellow for courses that the staff has attended but only after the deadline has passed.
 
carts
Many things are possible ... after to know what it look like.
I'll try to check others layout, cause You didn't know.
Be patient ...
 
carts
One more time ...
with #15 Reply: I asked YOU to show me a sample with Abel Silva
with #16 Reply: You wrote that since he has attended all courses that he needs to attend it would be left white in color.
with #18 Reply: You gave hint that ... hmm? ... there are non-whites?
Screen Shot 2018-04-28 at 18.20.06.png
> Do You know what would be needed output? <
It cannot be both ... colors and no colors in same time!
 
carts
So do I,
You wrote that Abel Silva no need colors
and next time You wrote that it needs.
It cannot be both!
Can You select
some names which NEEDS colors (and explain why) and
some names which NO NEED color (and explain why).
ps. Do not select Abel Silva!
 
Hi to both,

I'll try to facilitate.
Below I listed the formulae used in CF and those need to be replaced by a VBA code to colour the cells in columns C,D,E per Name in column A considering the deadline date in F.
  1. if cell is blank then no formatting
  2. Fill yellow: =COUNTIFS('Attendance list'!$A$2:$A$124,$A2,'Attendance list'!$B$2:$B$124,C2,'Attendance list'!$C$2:$C$124,">"&$F2)>0
  3. Fill red: =AND(COUNTIFS('Attendance list'!$A$2:$A$124,$A2,'Attendance list'!$B$2:$B$124,C2)=0,$F2<TODAY())
I got for Abel Silva this result, which seems to be correct
upload_2018-4-29_9-25-53.png
upload_2018-4-29_9-26-48.png

@vletm, does that help?
 
GraH - Guido
carts wrote that Abel Silva no need colors (#16 Reply
and next time carts wrote that it needs. (#18 Reply
> for me, challenge if same time 'no need' and 'need' <
... q: why there is today()? why not the deadline?
 
Back
Top