1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by carts, Apr 28, 2018.

  1. carts

    carts New Member

    Messages:
    18
    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!! :)

    Attached Files:

  2. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    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.

    Attached Files:

  3. carts

    carts New Member

    Messages:
    18
    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? :)
  4. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    Or, since you have the formulas in the CF, you can do that on your own.:)
  5. carts

    carts New Member

    Messages:
    18
    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:)
  6. Marc L

    Marc L Excel Ninja

    Messages:
    3,814
    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.
  7. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    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.
  8. carts

    carts New Member

    Messages:
    18
  9. carts

    carts New Member

    Messages:
    18
    @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?

    Attached Files:

  10. carts

    carts New Member

    Messages:
    18
    @GraH - Guido Can you help tag someone over here who you think can help me? ;)
  11. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    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?
    carts likes this.
  12. vletm

    vletm Excel Ninja

    Messages:
    3,519
  13. vletm

    vletm Excel Ninja

    Messages:
    3,519
    carts
    Could You make clear output sample?
  14. carts

    carts New Member

    Messages:
    18
    @vletm Hi! :)

    What do you mean?
  15. vletm

    vletm Excel Ninja

    Messages:
    3,519
    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
  16. carts

    carts New Member

    Messages:
    18
    @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.
  17. vletm

    vletm Excel Ninja

    Messages:
    3,519
    carts
    As written: ... or ANY NAME which would be marked?
    Show me sample WHO and HOW?
  18. carts

    carts New Member

    Messages:
    18
    @vletm GraH managed to do it just now, here's the file

    Attached Files:

  19. carts

    carts New Member

    Messages:
    18
    @vletm Is it possible to create a VBA routine? :)
  20. vletm

    vletm Excel Ninja

    Messages:
    3,519
    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 ...
  21. vletm

    vletm Excel Ninja

    Messages:
    3,519
    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!
  22. carts

    carts New Member

    Messages:
    18
    @GraH - Guido I am kind of confused with what @vletm is saying.. Can you help me out?
  23. vletm

    vletm Excel Ninja

    Messages:
    3,519
    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!
  24. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    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?
  25. vletm

    vletm Excel Ninja

    Messages:
    3,519
    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?

Share This Page