+ Reply to Thread
Results 1 to 10 of 10

Conditional Formatting

  1. #1
    Registered User
    Join Date
    09-20-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    75

    Conditional Formatting

    Dear friends,

    I always have trouble for conditional formatting.

    This time, I would like to highlighted the columns using conditional formatting, given a range in percentage. I have tried but to no avail.

    Can anyone assist?

    Student Grade
    Leon 91%
    Apple 112%
    Simone 81%
    Olivier 135%
    Terence 45%
    Violet 86%
    Pearl 103%
    Paul 122%
    Pamela 138%
    Andy 29%
    Richard 42%



    If between 0-90 Highlight Red
    If between 91-100 Higlight Green
    If between 101-110 Highight Orange
    If >110 Highlight Blue

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Conditional Formatting

    see attached workbook

    There are several ways to approach this.

    My way:
    - 4 rules - one for each condition
    - instead of percentages/decimal values I used number divided by 100 (90/100 etc)
    - to me this is clearer
    - use your own style

    CF rules are applied in sequence:
    - so you do not need to use "greater than this" and "less than that"
    - if greater than 0 then it's red
    then next rule over-rides it
    - so if it's also greater than 90 it's green
    etc
    NOTE - create > 0 rule first, then > 90, then > 100, then > 110
    or re-sequence after creating

    CF based on percent.jpg


    CF Percent result.jpg


    If (instead)you want names highlighted then the "Applies to" range is =$A$2:$A$12, but the rule formula does not change
    Attached Files Attached Files
    Last edited by kev_; 03-04-2017 at 04:34 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Registered User
    Join Date
    09-20-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Conditional Formatting

    Hi Kev,

    I just knew that for CF sequence is so important, that is why I could not get the result that I desired at first!

    Thanks so much for your explanation, really appreciate that!

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Conditional Formatting

    You are welcome
    I always try to keeps things in Excel as simple as possible - I'm less likely to make errors that way!

    If that solves your problem, please go to Thread tools at top of thread, and mark thread as solved.
    Thanks

  5. #5
    Registered User
    Join Date
    09-20-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Conditional Formatting

    Hi Kev,

    I am applying the exact same CF formula to other section (beside the grading section) within the same worksheet, however, it doesn't work. Could you advise?

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Conditional Formatting

    I could if I knew exactly what you are doing!

    But in principle:
    - highlight the range that you want to be formatted
    - the formula is the one that works for the TOP, LEFT cell of that range
    - then you need to decide whether any cell references should be relative or absolute
    ( and the logic for that is exactly the same as when you are copying and pasting formula in the worksheet itself)

    If you attach a workbook to your reply with a full explanation of what you are trying to format and the conditions I will explain it all to you
    To attach a file Click on Reply, then Go Advanced, then look below for manage attachments etc

    ManageAttachments.jpg

  7. #7
    Registered User
    Join Date
    09-20-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Conditional Formatting

    Sure, thanks!

    Did I manage to upload the file?
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Conditional Formatting

    see the images

    The percentages are held in column AE - the condition is based on those values

    So the formula needs to refer to values in that column, for example:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    AD is not a cell reference - that is what you input (see picture)

    To highlight names
    - select the cells in column AD starting at cell AD2
    - click on Conditional Formatting
    - the formula is
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    If your data starts in AD3, then the formula must also refer to row3 AE3 > 100/100

    If you want to make it easier (and there is nothing else in the column further down) then CF the whole column with:
    -select column AD
    -with formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    - and the Refers To range is =$AD:$AD






    CF_rule_error.jpg

    CF_values.jpg
    Last edited by kev_; 03-04-2017 at 07:24 AM.

  9. #9
    Registered User
    Join Date
    09-20-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Conditional Formatting

    Hi Kev,

    Thanks for your advice once again!

    Yes, I think I did a silly mistake!

  10. #10
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Conditional Formatting

    It's a frustrating but the "best" way to learn

+ 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: 1
    Last Post: 12-08-2016, 03:14 PM
  2. Replies: 9
    Last Post: 03-07-2016, 10:39 AM
  3. Conditional Formatting Removing Previous Conditional Formatting?
    By CravingGod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2016, 01:02 PM
  4. Replies: 6
    Last Post: 01-08-2016, 06:44 PM
  5. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  6. Replies: 1
    Last Post: 09-20-2013, 06:23 PM
  7. Replies: 3
    Last Post: 05-15-2012, 04:13 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