Results 1 to 7 of 7

Linking conditionally formatted cells from one to another maintaining the format

This is a discussion on Linking conditionally formatted cells from one to another maintaining the format within the Excel Questions forums, part of the Question Forums category; Hi, I think I need vba for this... In one spreadsheet I have conditionally formatted cells with a formula to ...

  1. #1
    New Member
    Join Date
    May 2013
    Posts
    4

    Default Linking conditionally formatted cells from one to another maintaining the format

    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!

  2. #2
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,783

    Default Re: Linking conditionally formatted cells from one to another maintaining the format

    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.
    Using Excel 2010, 2013

  3. #3
    New Member
    Join Date
    May 2013
    Posts
    4

    Default Re: Linking conditionally formatted cells from one to another maintaining the format

    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.
    Roster

     AB
    2ORG CHART NAMEYear Started
    3Smith2013
    4Jones2015
    5John2014
    6Otto2014
    7Jane2015
    8Bob2015
    9Bill2013
    10Cynthia2013
    11Walt2016
    12Agnes2015
    13Mike2013
    14Tim2013
    15Kathy2013


    Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


    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


    Excel tables to the web >> Excel Jeanie Html" 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.
    Last edited by adkinsj3; Jun 2nd, 2013 at 10:43 PM. Reason: Excel Jeanie code added

  4. #4
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,783

    Default Re: Linking conditionally formatted cells from one to another maintaining the format

    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.
    Using Excel 2010, 2013

  5. #5
    New Member
    Join Date
    May 2013
    Posts
    4

    Default Re: Linking conditionally formatted cells from one to another maintaining the format

    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.

  6. #6
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,783

    Default Re: Linking conditionally formatted cells from one to another maintaining the format

    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.
    Using Excel 2010, 2013

  7. #7
    New Member
    Join Date
    May 2013
    Posts
    4

    Default Re: Linking conditionally formatted cells from one to another maintaining the format

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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com