Method to highlight names with consecutive attendance

AlexCHI · Sep 29, 2017

I am using Excel 2007 and asking your help for the best (and the easiest) way to highlight the names of people who have 6-month of consecutive attendance every Sundays.

My worksheet's layout are as follow:
Column A: Last Name
Column B: First Name
Row 9: Dates (with "S9" as Jan 01, 2017 thru "NS9" as Dec 31, 2017)
Row 10: Days (with "S10" as Sunday thru "NS10" as Sunday)

The attendance is taken by marking an "X" in the Sunday columns (i.e., 1/01/2017, 1/08/2017, 1/15/2017 and so on). So whoever attends every Sunday for 6-month straight would have their names (last/first) in column A/B highlighted. I am not sure if conditional formatting can achieve this or VBA? I greatly appreciate your input and please kindly provide the steps to achieve this. Thank you in advance!

oldbrewer · Sep 29, 2017

01/01/17

08/01/17

15/01/17

22/01/17

29/01/17

05/02/17

12/02/17

19/02/17

26/02/17

05/03/17

12/03/17

19/03/17

26/03/17

02/04/17

09/04/17

16/04/17

23/04/17

30/04/17

dave

X

C

let us pretend we are looking for 4 OR MORE consecutive attendances

the cells that are colored are marked with a C as this forum does not support color

cond formula in E2

=AND(E2="X",D2="X",C2="X",B2="X")=TRUE

MickG · Sep 29, 2017

Try this:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG29Sep03
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("S11"), Range("S" & Rows.Count).End(xlUp))
Rng.Offset(, -18).Resize(, 2).Interior.Color = xlNone
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
 c = 0
 [COLOR="Navy"]For[/COLOR] Ac1 = 0 To 182 [COLOR="Navy"]Step[/COLOR] 7
    [COLOR="Navy"]For[/COLOR] Ac2 = Ac1 To Ac1 + 182 [COLOR="Navy"]Step[/COLOR] 7
       [COLOR="Navy"]If[/COLOR] Dn.Offset(, Ac2) = "X" [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            [COLOR="Navy"]If[/COLOR] c >= 27 [COLOR="Navy"]Then[/COLOR] Dn.Offset(, -18).Resize(, 2).Interior.Color = vbYellow
        [COLOR="Navy"]Else[/COLOR]
            c = 0
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac2
  [COLOR="Navy"]Next[/COLOR] Ac1
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

AlexCHI · Sep 30, 2017

Thank you for the code Mick. I had applied your VBA to the worksheet and tested the data with limited success though. I marked the "X" from the last Sunday in March thru the last Sunday in September (even thru October) and the name would not be highlighted. Also, I tested another subject name with "X" marked from January 01 thru July and indeed the name was highlighted but then I removed the "X"s from the first two Sundays' columns and ran the micro again and the name was still highlighted even though it should not.

So would you be so kind to address the followings?

Names (column A and column B) should be highlighted if there is 6-month straight consecutive attendance. Consecutive attendance can start any month in a year with emphasis on consecutive and not necessary starts from January. For example, a person's name will be highlighted if the attendance of this person starts on 3/19/2017 thru 9/24/2017.
My mistake, I had to add another column so please revise the code to reflect
- Row 9: Dates (with "T9" as Jan 01, 2017 thru "NT9" as Dec 31, 2017)
- Row 10: Days (with "T10" as Sunday thru "NT10" as Sunday)
- "T11" will be the first cell for attendance-taking
My mistake #2 , instead of taking attendance by marking "X"s, the cells will actually contain "AM", "PM" or "AM & PM".
Could you possibly make the VBA as a worksheet change event?

It would be so awesome if this really works! Thank you in advance for your time and effort to help, Mick!

AlexCHI · Sep 30, 2017

Thank you for the code Mick. I had applied your VBA to the worksheet and tested the data with limited success though. I marked the "X" from the last Sunday in March thru the last Sunday in September (even thru October) and the name would not be highlighted. Also, I tested another subject name with "X" marked from January 01 thru July and indeed the name was highlighted but then I removed the "X"s from the first two Sundays' columns and ran the micro again and the name was still highlighted even though it should not.

So would you be so kind to address the followings?

Names (column A and column B) should be highlighted if there is 6-month straight consecutive attendance. Consecutive attendance can start any month in a year with emphasis on consecutive and not necessary starts from January. For example, a person's name will be highlighted if the attendance of this person starts on 3/19/2017 thru 9/24/2017.
My mistake, I had to add another column so please revise the code to reflect
- Row 9: Dates (with "T9" as Jan 01, 2017 thru "NT9" as Dec 31, 2017)
- Row 10: Days (with "T10" as Sunday thru "NT10" as Sunday)
- "T11" will be the first cell for attendance-taking
My mistake #2 , instead of taking attendance by marking "X"s, the cells will actually contain "AM", "PM" or "AM & PM".
Could you possibly make the VBA as a worksheet change event?

It would be so awesome if this really works! Thank you in advance for your time and effort to help, Mick!

oldbrewer · Sep 30, 2017

my cond formatting code can be applied to the name, also.

AlexCHI · Sep 30, 2017

Hi Oldbrewer,

Thank you for your reply and formula. Since I am not well versed in the world of excel codes, would you mind walking me step by step as how I can achieve highlighting the names in column A and column B for those with 6-month consecutive Sunday attendances (with the worksheet layouts as described in post #4 )?

Thank you so much!

MickG · Sep 30, 2017

Try this:-

Code:

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A11"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rng.Offset(, 18).Resize(, 365)) [COLOR="Navy"]Is[/COLOR] Nothing And Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
Rng.Resize(, 2).Interior.Color = xlNone
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
 c = 0
 [COLOR="Navy"]For[/COLOR] Ac1 = 0 To 196 [COLOR="Navy"]Step[/COLOR] 7
    [COLOR="Navy"]For[/COLOR] Ac2 = Ac1 To Ac1 + 182 [COLOR="Navy"]Step[/COLOR] 7
        [COLOR="Navy"]If[/COLOR] UCase(Dn.Offset(, Ac2 + 18)) = "AM" Or UCase(Dn.Offset(, Ac2 + 18)) = "PM" Or UCase(Dn.Offset(, Ac2 + 18)) = "AM & PM" [COLOR="Navy"]Then[/COLOR]
            c = c + 1
           [COLOR="Navy"]If[/COLOR] c >= 26 [COLOR="Navy"]Then[/COLOR] Dn.Resize(, 2).Interior.Color = vbYellow
        [COLOR="Navy"]Else[/COLOR]
            c = 0
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac2
  [COLOR="Navy"]Next[/COLOR] Ac1
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

oldbrewer · Sep 30, 2017

do you want people who have ever had a 6 month run, or just those with a run of 6 months or more on THIS SUNDAY

AlexCHI · Oct 2, 2017

Hi Mick,

Thank you very much for the revised VBA code. However, this time the name was not highlighted at all after putting "AM" in the Sunday columns in a test subject's row for 6 consecutive months... In case the problem is due to my explanation, please refer to the screen shot of my worksheet. I really appreciate your time and help to debug the issue.

Excel 2007

A

B

S

T

U

V

W

X

Y

Z

AA

6

AM Attendance

3

1

7

PM Attendance

2

8

AM & PM Attendance

1

9

Jan-1

Jan-2

Jan-3

Jan-4

Jan-5

Jan-6

Jan-7

Jan-8

10

Last Name

First Name

Days Present

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

11

Brown

Charlie

1

AM & PM

12

Simpson

Bart

1

PM

13

Flintstone

Fred

1

AM

14

Boop

Betty

28

AM

Main Screen

MickG said:

Try this:-

Code:

Private [COLOR=Navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=Navy]As[/COLOR] Range)
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, Ac1 [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac2 [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Lst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A11"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]If[/COLOR] Not Intersect(Target, Rng.Offset(, 18).Resize(, 365)) [COLOR=Navy]Is[/COLOR] Nothing And Target.Count = 1 [COLOR=Navy]Then[/COLOR]
Rng.Resize(, 2).Interior.Color = xlNone
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
 c = 0
 [COLOR=Navy]For[/COLOR] Ac1 = 0 To 196 [COLOR=Navy]Step[/COLOR] 7
    [COLOR=Navy]For[/COLOR] Ac2 = Ac1 To Ac1 + 182 [COLOR=Navy]Step[/COLOR] 7
        [COLOR=Navy]If[/COLOR] UCase(Dn.Offset(, Ac2 + 18)) = "AM" Or UCase(Dn.Offset(, Ac2 + 18)) = "PM" Or UCase(Dn.Offset(, Ac2 + 18)) = "AM & PM" [COLOR=Navy]Then[/COLOR]
            c = c + 1
           [COLOR=Navy]If[/COLOR] c >= 26 [COLOR=Navy]Then[/COLOR] Dn.Resize(, 2).Interior.Color = vbYellow
        [COLOR=Navy]Else[/COLOR]
            c = 0
            [COLOR=Navy]Exit[/COLOR] For
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] Ac2
  [COLOR=Navy]Next[/COLOR] Ac1
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Method to highlight names with consecutive attendance

AlexCHI

New Member

Excel Facts

oldbrewer

Well-known Member

MickG

MrExcel MVP

AlexCHI

New Member

AlexCHI

New Member

oldbrewer

Well-known Member

AlexCHI

New Member

MickG

MrExcel MVP

oldbrewer

Well-known Member

AlexCHI

New Member

Similar threads

Forum statistics

Share this page

Method to highlight names with consecutive attendance

New Member

Excel Facts

Well-known Member

MrExcel MVP

New Member

New Member

Well-known Member

New Member

MrExcel MVP

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock