Hi Jerry,
I am using worksheet cells. Applying the CF rules to the new cells seem daunting and time consuming but I might be missing something.
Anyhow regarding your requests:
1. This is the table that is conditionally formatted in the Roster worksheet.
Roster
| A
| B
| C
| D
|
2
| ORG CHART NAME
| Year Started
| | Color Legend
|
3
| Smith
| 2013
| | 2012
|
4
| Jones
| 2015
| | 2013
|
5
| John
| 2014
| | 2014
|
6
| Otto
| 2014
| | 2015
|
7
| Jane
| 2015
| | 2016
|
8
| Bob
| 2015
| | 2017
|
9
| Bill
| 2013
| | 2018
|
10
| Cynthia
| 2013
| | 2019
|
11
| Walt
| 2016
| | 2020
|
12
| Agnes
| 2015
| | |
13
| Mike
| 2013
| | |
14
| Tim
| 2013
| | |
15
| Kathy
| 2013
| | |
The conditional codes are:
2012 (blue): =ISNUMBER(MATCH($B3,$D$3,0))
2013 (grey): =ISNUMBER(MATCH($B3,$D$4,0))
2014 (yellow): =ISNUMBER(MATCH($B3,$D$5,0))
2015 (lime green): =ISNUMBER(MATCH($B3,$D$6,0))
2016 (maroon): =ISNUMBER(MATCH($B3,$D$7,0))
2017 (orange): =ISNUMBER(MATCH($B3,$D$8,0))
2018 (purple): =ISNUMBER(MATCH($B3,$D$9,0))
2019 (tan): =ISNUMBER(MATCH($B3,$D$10,0))
2020 (teal): =ISNUMBER(MATCH($B3,$D$11,0))
I don't know why my colors transposed to slightly different ones in the copy and paste.
I'm not sure if the below means anything to you but this is what came from the clipboard when I used Excel Jeanie. Apologies that I am not as savvy with this.
Excel Workbook |
---|
|
---|
| A | B |
---|
2 | ORG CHART NAME | Year Started |
---|
3 | Smith | 2013 |
---|
4 | Jones | 2015 |
---|
5 | John | 2014 |
---|
6 | Otto | 2014 |
---|
7 | Jane | 2015 |
---|
8 | Bob | 2015 |
---|
9 | Bill | 2013 |
---|
10 | Cynthia | 2013 |
---|
11 | Walt | 2016 |
---|
12 | Agnes | 2015 |
---|
13 | Mike | 2013 |
---|
14 | Tim | 2013 |
---|
15 | Kathy | 2013 |
---|
|
---|
Excel
tables to the web >> Excel Jeanie
HTML 4
Roster
* | A | B |
2 | ORG CHART NAME | Year Started |
3 | Smith | 2013 |
4 | Jones | 2015 |
5 | John | 2014 |
6 | Otto | 2014 |
7 | Jane | 2015 |
8 | Bob | 2015 |
9 | Bill | 2013 |
10 | Cynthia | 2013 |
11 | Walt | 2016 |
12 | Agnes | 2015 |
13 | Mike | 2013 |
14 | Tim | 2013 |
15 | Kathy | 2013 |
<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 139px;"><col style="width: 139px;"></colgroup>
" target="_blank"> Excel Jeanie HTML 4
2. The Macro code is below for the Org Chart worksheet. Basically all I did was an equals to the cell in the Roster worksheet and manually colored the cell to match the year.
Sub OrgChartExample()
'
' OrgChartExample Macro
' For the conditional formatting help from Mr Excel
'
'
ActiveCell.FormulaR1C1 = "=Roster!R[-2]C"
Range("A8").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
This is the Org Chart worksheet view.
Org Chart
| A
| B
| C
| D
| E
| F
| G
| H
| I
| J
|
2
| | | | | | | | | | |
3
| | Staff Member A
| | Staff Member B
| | Staff Member C
| | | | |
4
| | Smith
| | Jones
| | John
| | | | |
5
| | | | | | | | | | |
6
| | | | | | | | | | |
7
| Dept
1
| Dept
2
| Dept
3
| Dept
4
| Dept
5
| Dept
6
| Dept
7
| Dept
8
| Dept
9
| Dept
10
|
8
| Otto
| Jane
| Bob
| Bill
| Cynthia
| Walt
| Agnes
| Mike
| Tim
| Kathy
|
Spreadsheet Formulas
|
Cell
| Formula
| B4
| =Roster!A3
| E4
| =Roster!A4
| H4
| =Roster!A5
| A8
| =Roster!A6
| B8
| =Roster!A7
| C8
| =Roster!A8
| D8
| =Roster!A9
| E8
| =Roster!A10
| F8
| =Roster!A11
| G8
| =Roster!A12
| H8
| =Roster!A13
| I8
| =Roster!A14
| J8
| =Roster!A15
|
|
Excel
tables to the web >>
Thank you in advance for looking at this.