+ Reply to Thread
Results 1 to 11 of 11

Conditional Formatting based on Formula Result

  1. 12-17-2017, 07:21 PM #1
    Arran [BMI]
    Arran [BMI] is offline
    Registered User
    Join Date
    12-10-2017
    Location
    Bungendore, NSW Australia
    MS-Off Ver
    2016
    Posts
    28

    Conditional Formatting based on Formula Result

    I would like assistance with some conditional formatting.

    What I would like to achieve is to highlight a cell dependant on a set criteria.

    Screen Shot 2017-12-15 at 13.43.14.png

    As you can see from the attached I would like to highlight the highest value in column H based on the Load Group selection (column D)

    For example.... I would like the cell that contains the value of "32" to be highlighted as the highest value for Load Group "D. Motors"

    Thanks in advance.
    Reply With QuoteRegister To Reply
  2. 12-17-2017, 08:35 PM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,742

    Re: Conditional Formatting based on Formula Result

    See the stripped down version and this formula in Format Manager.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave
    Reply With QuoteRegister To Reply
  3. 12-17-2017, 11:36 PM #3
    Arran [BMI]
    Arran [BMI] is offline
    Registered User
    Join Date
    12-10-2017
    Location
    Bungendore, NSW Australia
    MS-Off Ver
    2016
    Posts
    28

    Re: Conditional Formatting based on Formula Result

    Thank you for the help with this one. I have applied the formula and it works great. I have however discovered that when applied to the entire range, any cells that do not have any data in them also follow the selected formatting?

    So, based on the example that you have supplied. If you were to delete the values in D17 and H17, H17 also highlights.

    Any thoughts?
    Reply With QuoteRegister To Reply
  4. 12-17-2017, 11:51 PM #4
    Arran [BMI]
    Arran [BMI] is offline
    Registered User
    Join Date
    12-10-2017
    Location
    Bungendore, NSW Australia
    MS-Off Ver
    2016
    Posts
    28

    Re: Conditional Formatting based on Formula Result

    I have just done some more testing and found a couple of further issues. If I apply further conditional formatting to include the other load groups, (i.e. A. Lighting, B. Socket Outlets etc.) It will only highlight the highest value of any of the groups.

    So based on the result that you have supplied, if D. Motors does not have the overall highest value (i.e. one of the values in A. Lighting is higher) no cell will be highlighted.
    Reply With QuoteRegister To Reply
  5. 12-18-2017, 12:32 AM #5
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,742

    Re: Conditional Formatting based on Formula Result

    Quote Originally Posted by Arran [BMI] View Post
    Thank you for the help with this one. I have applied the formula and it works great. I have however discovered that when applied to the entire range, any cells that do not have any data in them also follow the selected formatting?

    So, based on the example that you have supplied. If you were to delete the values in D17 and H17, H17 also highlights.

    Any thoughts?
    Try this CF formula
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 12-18-2017, 12:38 AM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,742

    Re: Conditional Formatting based on Formula Result

    Quote Originally Posted by Arran [BMI] View Post
    I have just done some more testing and found a couple of further issues. If I apply further conditional formatting to include the other load groups, (i.e. A. Lighting, B. Socket Outlets etc.) It will only highlight the highest value of any of the groups.

    So based on the result that you have supplied, if D. Motors does not have the overall highest value (i.e. one of the values in A. Lighting is higher) no cell will be highlighted.
    Then with a drop down in F1 this CF formula.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 12-18-2017, 12:53 AM #7
    Arran [BMI]
    Arran [BMI] is offline
    Registered User
    Join Date
    12-10-2017
    Location
    Bungendore, NSW Australia
    MS-Off Ver
    2016
    Posts
    28

    Re: Conditional Formatting based on Formula Result

    Excellent, that has worked perfectly. Thank you for your assistance.
    Reply With QuoteRegister To Reply
  8. 12-18-2017, 05:22 PM #8
    Arran [BMI]
    Arran [BMI] is offline
    Registered User
    Join Date
    12-10-2017
    Location
    Bungendore, NSW Australia
    MS-Off Ver
    2016
    Posts
    28

    Re: Conditional Formatting based on Formula Result

    I have another quick question. Can the supplied formula, which works a treat be changed slightly to show other values such as the second highest?
    Reply With QuoteRegister To Reply
  9. 12-18-2017, 06:28 PM #9
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,742

    Re: Conditional Formatting based on Formula Result

    Yes.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    The 2 ... k value ... can be replaced with reference to a cell just as Load Groups are in the drop down.

    Just understand that if there are two MAX values the second LARGEst value is also the MAX.

    Did you want something else instead?
    Last edited by FlameRetired; 12-18-2017 at 06:33 PM.
    Reply With QuoteRegister To Reply
  10. 12-18-2017, 07:04 PM #10
    Arran [BMI]
    Arran [BMI] is offline
    Registered User
    Join Date
    12-10-2017
    Location
    Bungendore, NSW Australia
    MS-Off Ver
    2016
    Posts
    28

    Re: Conditional Formatting based on Formula Result

    Once again, many thanks... Worked a treat....
    Reply With QuoteRegister To Reply
  11. 12-18-2017, 09:40 PM #11
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,742

    Re: Conditional Formatting based on Formula Result

    You're welcome. Thanks for the feedback and marking your thread Solved. It helps.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional formatting based on the formula result value in a cell
    By scottli in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-27-2017, 05:25 PM
  2. [SOLVED] Conditional Formatting with a formula's result
    By rcanpolat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2016, 02:15 AM
  3. [SOLVED] Conditional formatting based on result of formula within the same cell
    By som3on3_10 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2014, 09:10 PM
  4. [SOLVED] Conditional formatting in one column based on the result of another
    By mra1984 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-05-2013, 09:41 AM
  5. Conditional formatting - more than 3 conditions - result based on another cell
    By Mostlygood in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-07-2013, 10:07 AM
  6. conditional formatting to change colour of cell based on formula result
    By gideong in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-15-2009, 09:35 AM
  7. conditional formatting based on another cells formula result
    By kstarkey in forum Excel General
    Replies: 3
    Last Post: 10-05-2005, 05:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1