+ Reply to Thread
Results 1 to 8 of 8

Formatting a cell based on the referenced cells fomat

  1. #1
    Registered User
    Join Date
    03-04-2015
    Location
    Webster, WI
    MS-Off Ver
    2007
    Posts
    10

    Formatting a cell based on the referenced cells fomat

    Hey guys and gals, I am trying to format a cell based off of the format of a cell that is being referenced. Right now I have a chart that when I type in a number, it cross-references that number in a column and row, then returns that number to the cell. Is there any way for it to recognize if the cell that is cross referenced is filled in "green" or if it is still not filled in? Sort of hard to explain, if you need a copy of the file I would be more that willing to send a copy for you to look at. Thanks in advance for the help....

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Formatting a cell based on the referenced cells fomat

    A tad confusing... Can you post your sheet here?Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Formatting a cell based on the referenced cells fomat

    If the cell that is filled in green is the result of Conditional Formatting then just use the same condition to format the new cell.
    If the green cell has not been set via Condtional Formattign then VBA is your only option to determine if the cell is green or not.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    03-04-2015
    Location
    Webster, WI
    MS-Off Ver
    2007
    Posts
    10

    Re: Formatting a cell based on the referenced cells fomat

    EXCELWB.png

    If the picture attached correctly, I would like to have Cell U5 formatted based on the format of the cell that is referenced by the formula in U5. As you can see 10 A10 is filled in green, so I would like U5 to turn green at that point. If I was looking up 14 A10, I would not want U5 to fill with green because 14 A10, is not filled green in the chart. Hopefully this will help explain what I am looking for.....

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Formatting a cell based on the referenced cells fomat

    Special K: Not quite... There's an old function still lurking in the background of Excel. It is limited and needs to be refreshed with F9 every time changes are made. But it does work. See attached.

    Theo893. I'm not sure if the GET.CELL function will allow a cell to be coloured, as you have requested in your update; but it can tell you if a target cell is colured or not. It is rather limited, and may not be applicable in your case.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 04-23-2015 at 12:30 PM.

  6. #6
    Registered User
    Join Date
    03-04-2015
    Location
    Webster, WI
    MS-Off Ver
    2007
    Posts
    10

    Re: Formatting a cell based on the referenced cells fomat

    Yes, I am not using conditional formatting for the original cell being filled. That will be done manually one at a time as they are checked and proven to contain the correct numbers.

    Here are the formulas for U5 and U6

    U5 = =IF(T3= " ", " ", INDEX($B$3:$H$52,IF(ISNUMBER(MATCH($T$3,$B$3:$B$52,0)),MATCH($T$3,$B$3:$B$52,0)-1,LOOKUP($T$3,$B$3:$B$52,ROW($B$3:$B$52)-MIN(ROW($B$3:$B$52))+1)),MATCH($V$3,$B$2:$H$2,0)))

    U6 = =IF(T3=" ", " ", INDEX($B$3:$H$52,IF(ISNUMBER(MATCH($T$3,$B$3:$B$52,0)),MATCH($T$3,$B$3:$B$52,0),LOOKUP($T$3,$B$3:$B$52,ROW($B$3:$B$52)-MIN(ROW($B$3:$B$52))+1)+1),MATCH($V$3,$B$2:$H$2,0)))

    Not sure is these are needed, but maybe there is a change to add to them....

  7. #7
    Registered User
    Join Date
    03-04-2015
    Location
    Webster, WI
    MS-Off Ver
    2007
    Posts
    10

    Re: Formatting a cell based on the referenced cells fomat

    Yes, I am not using conditional formatting for the original cell being filled. That will be done manually one at a time as they are checked and proven to contain the correct numbers.

    Here are the formulas for U5 and U6

    U5 = =IF(T3= " ", " ", INDEX($B$3:$H$52,IF(ISNUMBER(MATCH($T$3,$B$3:$B$52,0)),MATCH($T$3,$B$3:$B$52,0)-1,LOOKUP($T$3,$B$3:$B$52,ROW($B$3:$B$52)-MIN(ROW($B$3:$B$52))+1)),MATCH($V$3,$B$2:$H$2,0)))

    U6 = =IF(T3=" ", " ", INDEX($B$3:$H$52,IF(ISNUMBER(MATCH($T$3,$B$3:$B$52,0)),MATCH($T$3,$B$3:$B$52,0),LOOKUP($T$3,$B$3:$B$52,ROW($B$3:$B$52)-MIN(ROW($B$3:$B$52))+1)+1),MATCH($V$3,$B$2:$H$2,0)))

    Not sure is these are needed, but maybe there is a change to add to them....

  8. #8
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Formatting a cell based on the referenced cells fomat

    Then your only option is to use VBA to dtermine the format of the original cell

+ 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: 2
    Last Post: 01-21-2013, 02:35 AM
  2. [solved] Macro to apply formatting to each referenced cell of a formula
    By raystafarian in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-01-2012, 02:12 PM
  3. Replies: 4
    Last Post: 01-06-2012, 05:07 PM
  4. How to apply formatting to cell referenced text
    By dwain in forum Excel General
    Replies: 1
    Last Post: 07-17-2009, 12:14 PM
  5. [SOLVED] How do I have an image in a cell based upon a referenced value?
    By Jonathan Neubauer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-30-2005, 07:06 PM

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