+ Reply to Thread
Results 1 to 15 of 15

Make one celll inherit the format of another automatically

  1. #1
    Registered User
    Join Date
    06-07-2012
    Location
    NZ
    MS-Off Ver
    Excel 2010
    Posts
    4

    Make one celll inherit the format of another automatically

    Hello,

    I have created an excel spreadsheet that uses conditional formatting as you can see in the attachment.

    I want the all of the cells to the left of each colored cell to inherit the color from the cell of the bank balance automically.

    I hope I have sufficiently described what I need.

    Thank you, any help is appreciated.
    Attached Images Attached Images

  2. #2
    Valued Forum Contributor
    Join Date
    05-19-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: Make one celll inherit the format of another automatically

    If you could post a small example file ( not image ) with conditional formatting would help

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Make one celll inherit the format of another automatically

    Hi ratiug,

    Would that include empty cells to the left as well, or only cells containing Data? Either way, you could also use Conditional Formatting for that as well. BTW, it would be better if you could upload a sample WorkBook, instead of just an image.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  4. #4
    Registered User
    Join Date
    06-07-2012
    Location
    NZ
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Make one celll inherit the format of another automatically

    Yes exactly,

    Empty cells should change as well.

    And they should automatically respond to the conditional formatting on the bank balance column too.


    So I can use conditional formatting?

    Is there a conditional formatting option where I can just say 'if the format of cell x changes, change this cell to format of cell x'?

    Thanks.
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Make one celll inherit the format of another automatically

    yes, use "new rule" and then "use formula". you can specify in there what you want the condition to be in the other cell for the formatting to change. what i normally do if it's going to get involved/tricky, is to do is make it up in the worksheet itself 1st to test for TRUE/FALSE values that i want
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    06-07-2012
    Location
    NZ
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Make one celll inherit the format of another automatically

    The problem is that there is no real 'condition' per se,

    Its more like: 'set A5:G5 format to H5 format'.

    I believe in excel 2007 you could use a formula like A5:G5->$H5="In Progress"

    Is there a 2010 equivalent of this?

    Thanks.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Make one celll inherit the format of another automatically

    i dont believe you can set the format of 1 cell based just on the format of another cell, through either a formula or through CF. maybe some VBA code could do that, but i cant help with that

  8. #8
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Make one celll inherit the format of another automatically

    Hi ratiug,

    Thank you for the Sample.

    See if the attached is what you wanted?
    Attached Files Attached Files

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Make one celll inherit the format of another automatically

    @ Winon hi, hoe gaan dit? (ex PE here)

    from what i can make of the OP's requirements, they are looking for the format of a cell to change based on the format of another cell. looks like your CF is changing the format based on the value of the other cell?

    Ratiug, was this what you were after, or did i mis-understand your requirements?

  10. #10
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Make one celll inherit the format of another automatically

    @FDibbins,

    Dit gaan goed dankie, en met jou?

    I am a bit confused with your statement, as in itself you are confirming what the CF does.

    from what i can make of the OP's requirements, they are looking for the format of a cell to change based on the format of another cell. looks like your CF is changing the format based on the value of the other cell?
    The OP confirmed in Post #4

    Yes exactly,

    Empty cells should change as well.

    And they should automatically respond to the conditional formatting on the bank balance column too.
    and then in Post# 6

    Its more like: 'set A5:G5 format to H5 format'.
    I have just extended the Range to A5:G30. That is exactly what the Sheet now does, (I hope).

    Gaaf om kennis te maak. Bly in kontak

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Make one celll inherit the format of another automatically

    ok maybe i mis-understood (its happened before lol) i read it as he wanted to chage the format, based ON the format, not the value, but reading again, maybe they did mean the "value" and not the "format"

    Please Login or Register  to view this content.
    however, the sollution you provided seems to work well. often the way the OP describes the problem can be interpreted in a number of different ways.

    a quick top for the CF formula you used...you dont need to use the =if() function, =$H5<=0 works just as good

    waar in SA bly jy?

  12. #12
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Make one celll inherit the format of another automatically

    @ FDibbins,

    I have to agree:
    you dont need to use the =if() function, =$H5<=0 works just as good
    Will indeed work just as good, as long if the conditions hold true. But in the OP's case, it is not limited to only, =$H5<=0, as there are other criteria to be considered as well.

    Ek woon in die onder dorp, aan die verkeerde kant van die spoorlyn, in Springs, op 'n plot nogal

    Thank you for your contribution !

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Make one celll inherit the format of another automatically

    my ex lived in dunnottar, would often go thru there, i lived in Kdorp and Cvl, grew up in PE. good to meet people from my old home (live in USA now)

  14. #14
    Registered User
    Join Date
    06-07-2012
    Location
    NZ
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Make one celll inherit the format of another automatically

    Quote Originally Posted by Winon View Post
    Hi ratiug,

    Thank you for the Sample.

    See if the attached is what you wanted?
    Yes perfect,

    Thank you so much.

    I am just having a look at your IF statement now to try understand what you did

  15. #15
    Registered User
    Join Date
    10-19-2015
    Location
    Somerset
    MS-Off Ver
    Microsoft Office EXEL 2007
    Posts
    1

    Re: Make one celll inherit the format of another automatically

    It's a bit clunky but here is a method of doing what you want without the need to dive into a lot of complicated VBA coding.

    1. In your source data area add an extra hidden column containing the datum you want together with a code to indicate the type of formatting you want to replicate concatenated to the end.
    For example if cell A2 = "This datum" and cell B2 = 4 then if cell C2 contained the following formula,
    =LEFT(A2 & " ",20) & IF(B2>5,"1","2")
    then C2 would hold "This datum 1".

    2. Now in your VLOOKUP statement, or however else you are referring to the source data, rather than retrieve from the original data column, retrieve the new hidden column version (col. C in this example). You will need to size the target cells so that the tailing number does not show (which explains the additional spaces in the formula shown above)

    3. You can now use string manipulation formula in Conditional Formatting to control the format of the target cell.
    In the above example you might have something like =IF(RIGHT(X2,1)="1",TRUE,FALSE) >> fill cell red. Where X2 is the target cell.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1