+ Reply to Thread
Results 1 to 24 of 24

Formatting cell(s) based on another cell's conditional format?

  1. #1
    Registered User
    Join Date
    05-02-2011
    Location
    Australia
    MS-Off Ver
    Excel 2019
    Posts
    53

    Arrow Formatting cell(s) based on another cell's conditional format?

    Hello,

    If the colour of a number formatted cell is determined by CF based on it's numerical value (say using a 3-colour scale based on the cell), how can you have this same colour formatting dynamically apply to another cell )or cells) that is instead text formatted?

    Regards

    I-Like-Excel

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    Re: Formatting cell(s) based on another cell's conditional format?

    Use the same CF rule in the other cell
    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

  3. #3
    Registered User
    Join Date
    05-02-2011
    Location
    Australia
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Formatting cell(s) based on another cell's conditional format?

    Quote Originally Posted by FDibbins View Post
    Use the same CF rule in the other cell
    Using the same CF rule would be basing the format of the cell on the numerical value of that cell (not of another), which can be simply done by copy/pasting the CF formatting from one cell to the other. However the cell I want to format is however text. I can't see how a CF that is determined by a numerical value can work if the cell is instead text based.

    Please explain the method you have in mind.

    In case it may be unclear what I am asking: Imagine you have a cell with a regular 2-colour scale CF for values ranging between 0 (white) to 100 (red). That cell will take on a shaded colour depending on the numerical value of that cell. Now in an adjacent cell I type the text string "Temperature". I want the cell that contains the word "Temperature' to dynamically mirror the same shaded cell CF that is being applied to the adjacent numerical cell.
    Last edited by I-Like-Excel; 04-18-2021 at 10:59 AM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    Re: Formatting cell(s) based on another cell's conditional format?

    OK so what is the relationship between the number in cell "A" and the text in cell "B"? If there is no correlation between them (I cant see how "1" would relate to "temp"), how would "B" change based on what is in "A"?

    Prove a sample WB showing what you have and what you expect.

  5. #5
    Registered User
    Join Date
    05-02-2011
    Location
    Australia
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Formatting cell(s) based on another cell's conditional format?

    OK. The attached file should clearly illustrate what I am trying to do.

    https://www.excelforum.com/attachmen...1&d=1619156209
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    Re: Formatting cell(s) based on another cell's conditional format?

    You cannot use gradient scale with a formula. so my suggestion wont work

  7. #7
    Registered User
    Join Date
    05-02-2011
    Location
    Australia
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Formatting cell(s) based on another cell's conditional format?

    I am hoping to get a definitive response as to whether what I am trying to do is at all possible somehow. Does anyone else have another approach or compromised workaround or is this just simply impossible?

  8. #8
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

    Re: Formatting cell(s) based on another cell's conditional format?

    Hi I-Like-Excel,

    This is definitely possible, but I only know how to do this in VBA. Right click on the sheet you want to apply this to, choose "View Code" and paste the code in. Please see attached excel as reference.

    Please Login or Register  to view this content.

    Hope this helps.


    Edited: Redundancy in the code. Code and attachment updated.
    Attached Files Attached Files
    Last edited by D13L; 05-08-2021 at 03:48 AM.

  9. #9
    Registered User
    Join Date
    05-02-2011
    Location
    Australia
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Formatting cell(s) based on another cell's conditional format?

    Hello D13L,

    Thanks for your suggestion using VBA. I am not really well versed in things VBA except that coding VBA can help make your worksheets do things you cant otherwise do.

    I see that the code works if the value in the cell C columns are MANUALLY and DIRECTLY altered ie. you physically type in new a number in to the C column cell.

    However I have noted that if there is instead a FORMULA in the column C cells, the column D cells formatting does not DYNAMICALLY update when the value of the formula in the C column cells change,. For example, if a column C cell has for example the formula =A1+A3, where cells A1 and A2 contain numbers, changing the value of those numbers directly (which leads to the C cell value altering) does not trigger the VBA code to update the cell format in column D based on the new value in the adjacent column C cell.

    Can the code be slightly modified so that D cells formatting routine is triggered based on changes to the numerical value of whatever FORMULA exists in the C cells?
    Last edited by I-Like-Excel; 05-10-2021 at 02:18 AM.

  10. #10
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

    Re: Formatting cell(s) based on another cell's conditional format?

    Hi I-Like-Excel

    Please try:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-02-2011
    Location
    Australia
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Formatting cell(s) based on another cell's conditional format?

    Quote Originally Posted by D13L View Post
    Hi I-Like-Excel

    Please try:
    Hello again. Thanks but oddly it is behaving just like the original file you provided. The D column formats are not updating.

  12. #12
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

    Re: Formatting cell(s) based on another cell's conditional format?

    Hi I-Like-Excel

    I'm surprised. It's working for me for any changes made either column C or D.
    Would you mind checking if your Calculation is set to Automatic. (It's under ribbon: Formula > Calculations > Calculation Options).

    Also is your dataset in rows 5 - 15 only, or does it extend further. If it extends further then you have to set your min and max rows.

    Anyway I made a minor changed in the code. Hopefully it works:

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

    Re: Formatting cell(s) based on another cell's conditional format?

    Hi I-Like-Excel,

    I just realized that you are running 2007 of excel which appears to not support .DisplayFormat.Interior.Color

    I've looked online and can't seem to find a solution.

    I'm sorry but the code above won't work you.

  14. #14
    Registered User
    Join Date
    05-02-2011
    Location
    Australia
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Formatting cell(s) based on another cell's conditional format?

    Hello D13L,

    Thanks for the help! I actually didn't realise that myself! All good, it will work on latest version!

  15. #15
    Registered User
    Join Date
    05-02-2011
    Location
    Australia
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Formatting cell(s) based on another cell's conditional format?

    Quote Originally Posted by D13L View Post
    Hi I-Like-Excel,

    I just realized that you are running 2007 of excel which appears to not support .DisplayFormat.Interior.Color

    I've looked online and can't seem to find a solution.

    I'm sorry but the code above won't work you.
    Hello D13L,

    I just opened up the file on a 2019 Excel install and it didn't work there either. Should it? If anyone else is reading this (and I see over 3000 of you have been), it would help if you can download the file above and advise if it works for you by replying to this thread.
    Last edited by I-Like-Excel; 05-15-2021 at 04:57 AM.

  16. #16
    Registered User
    Join Date
    05-02-2011
    Location
    Australia
    MS-Off Ver
    Excel 2019
    Posts
    53

    Question Re: Formatting cell(s) based on another cell's conditional format?

    Hello All,

    If you reading this please download the file below and let me know if the conditional formatting in column D automatically updates for you.

    Thanks

    I-Like-Excel
    Attached Files Attached Files

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Formatting cell(s) based on another cell's conditional format?

    Can you please clarify: are you using the old Excel 2007? If not, please update your profile.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  18. #18
    Registered User
    Join Date
    05-02-2011
    Location
    Australia
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Formatting cell(s) based on another cell's conditional format?

    I am now using Excel 2019. Still none the wiser if file works for others. It doesn't work for me.

  19. #19
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

    Re: Formatting cell(s) based on another cell's conditional format?

    Hi I-Like-Excel,

    I'm sorry to hear that it doesn't work fork you. I'm baffled myself since I'm using 2019 myself. The only thing I can think of is that macro is not enabled: https://www.ablebits.com/office-addi...-macros-excel/


    I wanted to attached a gif animation where it is working for me but somehow Im getting an error when I upload .zip files.



    EDITED: Added a very trimmed gif animation to meet 100kb limit
    Attached Images Attached Images
    Last edited by D13L; 05-31-2021 at 07:55 AM.

  20. #20
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    145

    Re: Formatting cell(s) based on another cell's conditional format?

    Hi I-Like-Excel,

    I noticed that the code doesn't run when the F9 key is pressed(Or worksheet refresh).

    I'm assuming this is the issue you are encountering. If so please replace code with this:

    EDITED: Remove unnecessary excess in code.
    Please Login or Register  to view this content.
    Last edited by D13L; 05-31-2021 at 08:49 AM.

  21. #21
    Registered User
    Join Date
    05-02-2011
    Location
    Australia
    MS-Off Ver
    Excel 2019
    Posts
    53

    Re: Formatting cell(s) based on another cell's conditional format?

    YAY!!!! It now works!!! Thank you!!!!

  22. #22
    Registered User
    Join Date
    03-01-2022
    Location
    Geelong
    MS-Off Ver
    365
    Posts
    1

    Re: Formatting cell(s) based on another cell's conditional format?

    I see you have a solution that is working, and perhaps I don't have quite get the nuances of your problem, but as a potential quick solution for the next person who comes across this thread, you could try the following:

    If you want cells B1:B10 to format from text in A1:A10, you could use a conditional formatting formula:
    RULE [=OR(A1="negative")] Format [red text] Applies to [=$B$1:$B$10]
    RULE [=OR(A1="positive")] Format [green text] Applies to [=$B$1:$B$10]

    Capture Excel.JPG

  23. #23
    Registered User
    Join Date
    02-26-2014
    Location
    South Dakota
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Formatting cell(s) based on another cell's conditional format?

    Quote Originally Posted by D13L View Post
    Hi I-Like-Excel,

    I noticed that the code doesn't run when the F9 key is pressed(Or worksheet refresh).

    I'm assuming this is the issue you are encountering. If so please replace code with this:

    EDITED: Remove unnecessary excess in code.
    Please Login or Register  to view this content.
    Could you modify this to work for a multi-column range? Example: my conditionally formatted data is in A1:D10. I would like the color displayed to be copied over to AA1:AD10. My ranges are both the same size.

  24. #24
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,990

    Re: Formatting cell(s) based on another cell's conditional format?

    Quote Originally Posted by DeZeeuw2 View Post
    Could you modify this to work for a multi-column range? Example: my conditionally formatted data is in A1:D10. I would like the color displayed to be copied over to AA1:AD10. My ranges are both the same size.
    If you are asking for that to be modified to suite your needs, please dont highjack other member's threads.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 9
    Last Post: 09-12-2020, 12:10 PM
  2. Conditional Formatting Based on Adjacent Cell *Format*
    By MacroPolo in forum Excel General
    Replies: 1
    Last Post: 05-18-2016, 09:59 AM
  3. Replies: 2
    Last Post: 07-28-2013, 03:28 PM
  4. [SOLVED] Workaround to Conditional Formatting based on a Format of another cell
    By sarkman22 in forum Excel General
    Replies: 5
    Last Post: 05-10-2013, 01:24 PM
  5. Using Conditional Formatting to format range based on one cell
    By panamakevin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-28-2012, 09:50 PM
  6. Replies: 2
    Last Post: 12-21-2011, 06:01 PM
  7. Conditional Formatting 4+ conditions, format rows based on cell
    By nockam in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-12-2006, 06:12 PM

Tags for this Thread

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