Linking conditionally formatted cells from one to another maintaining the format

[adkinsj3]

Hi,
I think I need vba for this...

In one spreadsheet I have conditionally formatted cells with a formula to highlight the name of a staff member as well as their start year based on the year. For example:
2013 = yellow, 2014 = grey, 2015 = green. This works like a charm with the ISNUMBER(MATCH......) formula.

I subsequently have an org chart in another worksheet and want to link the name to the cell in the other worksheet AND maintain the color from the conditional formatting. I think this is possible with a vba code but I can't find one out there.

I have an example that I can provide as an attachment if necessary.

Thank you!

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Are you using worksheet cells for your Org Chart, or drawing objects?

If the org chart uses cells, then you could just apply the same CF rules to those cells.

If they are drawing objects, you'll probably need VBA. If you want some help with code for this, please...
1. Post the formulas in your CF rules
2. Use the macro recorder to record the steps of you manually changing the color of one of the drawing objects and post that recorded code.

 

[adkinsj3]

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
Roster

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.

 

[Jerry Sullivan]

Thanks, that helps in understanding your setup.

First, The CF formulas on the Roster sheet can be simplified. There's no need to do a Match on a one-cell range. Instead just test if the lookup and the one-cell range are equal.
The simplified CF rules would be like...
2012 (blue): =$D$3
2013 (grey): =$D$4
2014 (yellow): =$D$5

For the CF on the Org Chart you could use:
Applies to: =$A$4:$J$4,$A$9:$J$15 (allowing room to add more rows)
2012 (blue): =VLOOKUP(A4,Roster!$A$3:$B$15,2,0)=Roster!$D$3
2013 (grey): =VLOOKUP(A4,Roster!$A$3:$B$15,2,0)=Roster!$D$4
2014 (yellow): =VLOOKUP(A4,Roster!$A$3:$B$15,2,0)=Roster!$D$5

If you want to make this more adaptable to resizing of your lookups, you could use Named Ranges for your Lookup (A$3:$B$15) and/or your Color Legend. The CF rules might then look like....
2012 (blue): =VLOOKUP(A4,MyRoster,2,0)=INDEX(MyColors,1)
2013 (grey): =VLOOKUP(A4,MyRoster,2,0)=INDEX(MyColors,2)
2014 (yellow): =VLOOKUP(A4,MyRoster,2,0)=INDEX(MyColors,3)

Of course this could be done with VBA too, but I always like to offer a non-VBA approach when it's feasible.

 

[adkinsj3]

I used the match on a two-cell range so that when the year is changed, the name and year correspond to the color. I did this initially thinking that the name color formatting could be somehow linked to the name on the org chart.

As far as the vlookup solution for the CF on the Org Chart, I am totally onboard with that logic...it makes perfect sense however the CF is not capturing the color for some reason. When I tested out the formula it produces a TRUE result so I'm not sure where the disconnect is with the CF.

 

[Jerry Sullivan]

A common mistake with CF is not aligning the Formula and the Applies to: Range when the formula includes relative references.

The formula examples I posted should work if you use the together, for example...
2012 (blue): =VLOOKUP(A4,MyRoster,2,0)=INDEX(MyColors,1)
Applies to: =$A$4:$J$4,$A$9:$J$15

If you instead try to test this with just ...
Applies to: =$A$9:$J$15

Then the formulas will be off by 5 rows.

 

[adkinsj3]

Jerry, you are AWESOME! This rocks! Thank you so much.