Trouble highlighting row data.

[Davio]

TABLE1		J	A	N	U	A	R	Y
	1	2	3	4	5	6	7	8	9	10
TEAM1	X	X	X				X	X
TEAM2			X	X	X	X	X
TEAM3									X	X

<tbody>
</tbody>

TABLE2	TASK	START DATE	END DATE
TEAM1	Y	1jan	3jan
TEAM2	X	3jan	7jan
TEAM3	B	9jan	10jan
TEAM1	X	7jan	8jan

<tbody>
</tbody>

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]

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

Then I would stick with post 15.

 

[Davio]

Thank you.

 

[Davio]

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]

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

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	10-Sep
26
Tables (wsc1)

 

[Davio]

After following the instructions, there are no changes.

Disregard,

It works perfectly. Thank you

 

[Peter_SSs]

It works perfectly. Thank you

Good to hear. You're very welcome.

 

[Davio]

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]

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]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2		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	TEAM-1										STRENGTH
5	TEAM-2
6	TEAM-3
7	TEAM-4										VACATION-VACANT
8	TEAM-5	NO TRAINER AVAILABLE
9	MISC EVENTS
10	Report Period	Sep-19
11
12
13
14
15
16
17	TEAM NAME	TRAINING				START			END
18	TEAM-1	STRENGTH				10-Sep-19			20-Sep-19
19	TEAM-2	CARDIO				25-Sep-19			2-Oct-19
20	TEAM-3	ENDURANCE				7-Oct-19			15-Oct-19
21	TEAM-4	VACATION-VACANT				10-Sep-19			10-Oct-19
22	TEAM-5	NO TRAINER AVAILABLE				1-Sep-19			22-Oct-19
23	MISC EVENTS	HALLOWEEN SPECIAL				25-Oct-19			31-Oct-19
24	TEAM-1	ENDURANCE				7-Oct-19			15-Oct-19
25	TEAM-2	STRENGTH				19-Oct-19			29-Oct-19

<tbody>
</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:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
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>
</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]

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