Trouble highlighting row data.

Davio · Aug 28, 2019

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]TABLE1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD]TEAM1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]TEAM2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]TEAM3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]TABLE2[/TD]
[TD="align: center"]TASK[/TD]
[TD="align: center"]START DATE[/TD]
[TD="align: center"]END DATE[/TD]
[/TR]
[TR]
[TD]TEAM1[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]1jan[/TD]
[TD="align: center"]3jan[/TD]
[/TR]
[TR]
[TD]TEAM2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]3jan[/TD]
[TD="align: center"]7jan[/TD]
[/TR]
[TR]
[TD]TEAM3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]9jan[/TD]
[TD="align: center"]10jan[/TD]
[/TR]
[TR]
[TD]TEAM1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]7jan[/TD]
[TD="align: center"]8jan[/TD]
[/TR]
</tbody>[/TABLE]

I am having issues with TABLE1 highlighting the start and end dates from TABLE2. My goal is to have the data from TABLE2 visualized in TABLE1 The task data does not factor in to the visualization represented in TABLE1. any help or advice is appreciated.

Thank you.

Peter_SSs · Aug 30, 2019

Davio said:
I want to keep this simple and stay away from VBA if possible.

Then I would stick with post 15.

Davio · Aug 30, 2019

Thank you.

Davio · Aug 31, 2019

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

BL

BM

BN

BO

BP

BQ

BR

BS

BT

BU

BV

BW

BX

BY

1

2

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

3

1-Sep-19

2-Sep-19

3-Sep-19

4-Sep-19

5-Sep-19

6-Sep-19

7-Sep-19

8-Sep-19

9-Sep-19

10-Sep-19

11-Sep-19

12-Sep-19

13-Sep-19

14-Sep-19

15-Sep-19

16-Sep-19

17-Sep-19

18-Sep-19

19-Sep-19

20-Sep-19

21-Sep-19

22-Sep-19

23-Sep-19

24-Sep-19

25-Sep-19

26-Sep-19

27-Sep-19

28-Sep-19

29-Sep-19

30-Sep-19

1-Oct-19

2-Oct-19

3-Oct-19

4-Oct-19

5-Oct-19

6-Oct-19

7-Oct-19

8-Oct-19

9-Oct-19

10-Oct-19

11-Oct-19

12-Oct-19

13-Oct-19

14-Oct-19

15-Oct-19

16-Oct-19

17-Oct-19

18-Oct-19

19-Oct-19

20-Oct-19

21-Oct-19

22-Oct-19

23-Oct-19

24-Oct-19

25-Oct-19

26-Oct-19

27-Oct-19

28-Oct-19

29-Oct-19

30-Oct-19

31-Oct-19

1-Nov-19

2-Nov-19

3-Nov-19

4-Nov-19

5-Nov-19

6-Nov-19

7-Nov-19

8-Nov-19

9-Nov-19

10-Nov-19

11-Nov-19

12-Nov-19

13-Nov-19

14-Nov-19

15-Nov-19

4

TEAM1

5

TEAM2

6

TEAM3

7

TEAM4

8

TEAM5

9

TEAM6

10

MISC EVENTS

11

Report Period

Sep-19

Oct-19

Nov-19

12

13

14

15

16

17

18

TEAM

PROJECT

START DATE

END DATE

19

TEAM1

CROSSFIT

3-Sep-19

10-Oct-19

20

TEAM2

STRENGTH TNG

10-Sep-19

15-Oct-19

21

TEAM3

CARDIO

1-Oct-19

20-Oct-19

22

TEAM4

NUTRITION

30-Oct-19

10-Nov-19

23

TEAM5

10-Nov-19

12-Nov-19

24

TEAM6

15-Nov-19

19-Nov-19

25

MISC EVENTS

10-Sep-19

3-Nov-19

26

27

28

29

30

31

32

33

TRAINING

Peter,

Looking at the tracker, I think it will be better if I have the "projects" displayed across the date range. I have no experience with vba or macros. Where do I begin?

Peter_SSs · Aug 31, 2019

Davio said:
Looking at the tracker, I think it will be better if I have the "projects" displayed across the date range. I have no experience with vba or macros. Where do I begin?

For the layout you have just posted, try this in a copy of the workbook. It is a Worksheet_Change event code which means it is triggered when you change anything on the worksheet. It looks (or should) to see if anything was changed in that bottom section (below the 'Team Name' heading in A18) & if so rebuilds the top tracker section. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test. (To start with copy the bottom section and re-paste it to trigger the code. Then try adding/deleting/altering dates, team names Projects etc)
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

The colours that get applied to each team are determined by the 'Const' line near the start of the code and the choice of colours can be seen here

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, rw As Range, cellTeam As Range, rAnchorDate As Range, rTracker As Range, rData As Range
  Dim rwTeam As Long, NumDays As Long, TeamClr As Long, AnchorDate As Long, AnchorCol As Long, StartDateCol As Long, oSet As Long, lc As Long
  Dim TeamName As String
  Dim aColours As Variant
  
  Const TeamColours As String = "$Team1:50|$TEAM2:4|$Team3:33|$TEAM4:6|$TEAM5:22|$Team6:45|$Misc Events:35" '<- Adjust team names and colour values here

  Set rData = Columns("A").Find(What:="Team Name", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Offset(1)
  Set rData = rData.Resize(Cells.SpecialCells(xlCellTypeLastCell).Row - rData.Row + 1, 7)
  Set Changed = Intersect(Target, rData)
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    On Error GoTo AEET
    lc = Cells(3, Columns.Count).End(xlToLeft).Column
    Set rTracker = Range("B4").Resize(Columns("A").Find(What:="Report Period", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Row - 4, lc - 1)
    Set rAnchorDate = rTracker.Cells(0, 1)
    AnchorDate = rAnchorDate.Value
    AnchorCol = rAnchorDate.Column
    aColours = Split(TeamColours, "|")
    With rTracker
      .ClearContents
      .Interior.ColorIndex = xlNone
      .HorizontalAlignment = xlGeneral
    End With
    For Each rw In rData.Rows
      TeamName = rw.Cells(1).Value
      If Len(TeamName) > 0 Then
        Set cellTeam = Range("A4:A17").Find(What:=TeamName, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not cellTeam Is Nothing Then
          rwTeam = cellTeam.Row
          
          NumDays = rw.Cells(7).Value - rw.Cells(6).Value + 1
          oSet = rw.Cells(6).Value - AnchorDate
          StartDateCol = AnchorCol + oSet
          With Cells(rwTeam, StartDateCol).Resize(, NumDays)
            .Cells(1).Value = rw.Cells(2).Value
            .HorizontalAlignment = xlCenterAcrossSelection
            If InStr(1, TeamColours, "$" & TeamName & ":", vbTextCompare) > 0 Then
               TeamClr = Split(Mid(TeamColours, InStr(1, TeamColours, "$" & TeamName & ":", vbTextCompare) + Len(TeamName) + 2), "|")(0)
               .Interior.ColorIndex = TeamClr
            End If
          End With
        End If
      End If
    Next rw
  End If
AEET:
  Application.EnableEvents = True
End Sub

Here is part of my sheet after I have entered my data at the bottom. Note that I have changed that data so you can see the results in a screen shot that is not too large.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

3

01-Sep-19

02-Sep-19

03-Sep-19

04-Sep-19

05-Sep-19

06-Sep-19

07-Sep-19

08-Sep-19

09-Sep-19

10-Sep-19

11-Sep-19

12-Sep-19

13-Sep-19

14-Sep-19

15-Sep-19

16-Sep-19

4

TEAM1

CROSSFIT

5

TEAM2

STRENGTH TNG

6

TEAM3

CARDIO

NUTRITION

7

TEAM4

8

TEAM5

9

TEAM6

Other 1

10

MISC EVENTS

Other 2

11

Report Period

12

16

17

18

Team Name

Project

START DATE

END DATE

19

TEAM1

CROSSFIT

1-Sep

8-Sep

20

TEAM2

STRENGTH TNG

10-Sep

15-Sep

21

TEAM3

CARDIO

2-Sep

5-Sep

22

TEAM3

NUTRITION

9-Sep

15-Sep

23

TEAM4

4-Sep

7-Sep

24

TEAM6

Other 1

7-Sep

16-Sep

25

MISC EVENTS

Other 2

10-Sep

26

Tables (wsc1)

Davio · Sep 1, 2019

After following the instructions, there are no changes.

Disregard,

It works perfectly. Thank you

Peter_SSs · Sep 1, 2019

Davio said:
It works perfectly. Thank you

Good to hear. You're very welcome.

Davio · Sep 11, 2019

Peter,

I am trying to update the spreadsheet to compile all information to display the ranges on a summary page.

So far, I've made duplicate sheets with different tab (Name A, Name B, Name C, Name D, Name E, Name F, Name G) names. Now I want only the calendar section from each sheet (tab) to display into one summary calendar. I have adjusted some of the team names to reflect which tab they are pulled from.

Peter_SSs · Sep 13, 2019

Davio said:
Peter,

I am trying to update the spreadsheet to compile all information to display the ranges on a summary page.

So far, I've made duplicate sheets with different tab (Name A, Name B, Name C, Name D, Name E, Name F, Name G) names. Now I want only the calendar section from each sheet (tab) to display into one summary calendar. I have adjusted some of the team names to reflect which tab they are pulled from.

Sorry, I don't understand exactly what your setup & requirement is. Perhaps you could post some very small examples & results to help demonstrate?

Davio · Sep 13, 2019

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P

<tbody>
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: right"]Sun[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tue[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thu[/TD]
[TD="align: right"]Fri[/TD]
[TD="align: right"]Sat[/TD]
[TD="align: right"]Sun[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tue[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thu[/TD]
[TD="align: right"]Fri[/TD]
[TD="align: right"]Sat[/TD]
[TD="align: right"]Sun[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1-Sep-19[/TD]
[TD="align: center"]2-Sep-19[/TD]
[TD="align: center"]3-Sep-19[/TD]
[TD="align: center"]4-Sep-19[/TD]
[TD="align: center"]5-Sep-19[/TD]
[TD="align: center"]6-Sep-19[/TD]
[TD="align: center"]7-Sep-19[/TD]
[TD="align: center"]8-Sep-19[/TD]
[TD="align: center"]9-Sep-19[/TD]
[TD="align: center"]10-Sep-19[/TD]
[TD="align: center"]11-Sep-19[/TD]
[TD="align: center"]12-Sep-19[/TD]
[TD="align: center"]13-Sep-19[/TD]
[TD="align: center"]14-Sep-19[/TD]
[TD="align: center"]15-Sep-19[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]TEAM-1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #339966"]STRENGTH[/TD]
[TD="bgcolor: #339966"][/TD]
[TD="bgcolor: #339966"][/TD]
[TD="bgcolor: #339966"][/TD]
[TD="bgcolor: #339966"][/TD]
[TD="bgcolor: #339966"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]TEAM-2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]TEAM-3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]TEAM-4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]VACATION-VACANT[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]TEAM-5[/TD]
[TD="bgcolor: #FF8080"]NO TRAINER AVAILABLE[/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]
[TD="bgcolor: #FF8080"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]MISC EVENTS[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]Report Period[/TD]
[TD="align: center"]Sep-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]TEAM NAME[/TD]
[TD="align: center"]TRAINING[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]START[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]END[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]TEAM-1[/TD]
[TD="align: center"]STRENGTH[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]10-Sep-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]20-Sep-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]TEAM-2[/TD]
[TD="align: center"]CARDIO[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]25-Sep-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2-Oct-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]TEAM-3[/TD]
[TD="align: center"]ENDURANCE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7-Oct-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]15-Oct-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]TEAM-4[/TD]
[TD="align: center"]VACATION-VACANT[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]10-Sep-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]10-Oct-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]TEAM-5[/TD]
[TD="align: center"]NO TRAINER AVAILABLE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1-Sep-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]22-Oct-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]MISC EVENTS[/TD]
[TD="align: center"]HALLOWEEN SPECIAL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]25-Oct-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]31-Oct-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]TEAM-1[/TD]
[TD="align: center"]ENDURANCE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7-Oct-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]15-Oct-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]TEAM-2[/TD]
[TD="align: center"]STRENGTH[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]19-Oct-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]29-Oct-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

TABLE 1 TAB (NAME A)

VBA Code 1
-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range, rw As Range, cellTeam As Range, rAnchorDate As Range, rTracker As Range, rData As Range
Dim rwTeam As Long, NumDays As Long, TeamClr As Long, AnchorDate As Long, AnchorCol As Long, StartDateCol As Long, oSet As Long, lc As Long
Dim TeamName As String
Dim aColours As Variant

Const TeamColours As String = "$TEAM-1:50|$TEAM-2:4|$TEAM-3:33|$TEAM-4:6|$TEAM-5:22|$TEAM-6:45|$Misc Events:35" '<- Adjust team names and colour values here
Set rData = Columns("A").Find(What:="Team Name", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Offset(1)
Set rData = rData.Resize(Cells.SpecialCells(xlCellTypeLastCell).Row - rData.Row + 1, 9)
Set Changed = Intersect(Target, rData)
If Not Changed Is Nothing Then
Application.EnableEvents = False
On Error GoTo AEET
lc = Cells(3, Columns.Count).End(xlToLeft).Column
Set rTracker = Range("B4").Resize(Columns("A").Find(What:="Report Period", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Row - 4, lc - 1)
Set rAnchorDate = rTracker.Cells(0, 1)
AnchorDate = rAnchorDate.Value
AnchorCol = rAnchorDate.Column
aColours = Split(TeamColours, "|")
With rTracker
.ClearContents
.Interior.ColorIndex = xlNone
.HorizontalAlignment = xlGeneral
End With
For Each rw In rData.Rows
TeamName = rw.Cells(1).Value
If Len(TeamName) > 0 Then
Set cellTeam = Range("A4:A10").Find(What:=TeamName, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not cellTeam Is Nothing Then
rwTeam = cellTeam.Row

NumDays = rw.Cells(9).Value - rw.Cells(6).Value + 1
oSet = rw.Cells(6).Value - AnchorDate
StartDateCol = AnchorCol + oSet
With Cells(rwTeam, StartDateCol).Resize(, NumDays)
.Cells(1).Value = rw.Cells(2).Value
.HorizontalAlignment = xlCenterAcrossSelection
If InStr(1, TeamColours, "$" & TeamName & ":", vbTextCompare) > 0 Then
TeamClr = Split(Mid(TeamColours, InStr(1, TeamColours, "$" & TeamName & ":", vbTextCompare) + Len(TeamName) + 2), "|")(0)
.Interior.ColorIndex = TeamClr
End If
End With
End If
End If
Next rw
End If
AEET:
Application.EnableEvents = True
--------------------------------------

TABLE 2:


1	Report Period	Sep-19
2	2019-2020	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
3		1-Sep-19	2-Sep-19	3-Sep-19	4-Sep-19	5-Sep-19	6-Sep-19	7-Sep-19	8-Sep-19	9-Sep-19	10-Sep-19	11-Sep-19	12-Sep-19	13-Sep-19	14-Sep-19	15-Sep-19
4	NAME A
5
6
7
8
9
10
11	NAME B
12
13
14
15
16
17
18	NAME C
19
20
21
22
23
24
25	NAME D
26
27
28
29
30

<tbody>
[TH="align: left"][/TH]
[TH="align: left"]A[/TH]
[TH="align: left"]B[/TH]
[TH="align: left"]C[/TH]
[TH="align: left"]D[/TH]
[TH="align: left"]E[/TH]
[TH="align: left"]F[/TH]
[TH="align: left"]G[/TH]
[TH="align: left"]H[/TH]
[TH="align: left"]I[/TH]
[TH="align: left"]J[/TH]
[TH="align: left"]K[/TH]
[TH="align: left"]L[/TH]
[TH="align: left"]M[/TH]
[TH="align: left"]N[/TH]
[TH="align: left"]O[/TH]
[TH="align: left"]P[/TH]

[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]

[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]

[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #000000"][/TD]

</tbody>

SUMMARY SHEET

TABLE 1 shows one of seven duplicate tabs which shows each coach's team schedule. Each tab also has the VBA code included above. My goal is to have the tab name and calendar portion of each coach's tab populate on one summary sheet as in TABLE 2. NAME A is merged from A4:A9, NAME B A11:A16, NAME C A18:A23, etc. I hope this clears thing up a bit.

Peter_SSs · Sep 14, 2019

Sorry, I'm afraid I still don't "get it" so am unable to advise further.

Trouble highlighting row data.

Davio

New Member

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

Davio

New Member

Davio

New Member

Peter_SSs

MrExcel MVP, Moderator

Davio

New Member

Peter_SSs

MrExcel MVP, Moderator

Davio

New Member

Peter_SSs

MrExcel MVP, Moderator

Davio

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Trouble highlighting row data.

New Member

MrExcel MVP, Moderator

Excel Facts

New Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

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