Results 1 to 9 of 9

Thread: Conditional Formatting Formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Conditional Formatting Formula

    Dear Forum

    I have a table of data; Columns G to M. This range contains prices, however some columns have no prices so are blank

    I have added conditional formatting to highlight the lowest price using the Min function, however this highlights all of the blank cells

    I have tried to correct this by adding another formula within the conditional formatting box that uses ISBlank and have ticked the Stop if True button. This almost works however if you enter a price in say G2 and it is the lowest and enter a lower price in say J2, the conditional formatting moves off G2 but does not move to J2.

    This is driving me crazy, therefore I would appreciate anyone's help.

    Regards

    Dave

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Conditional Formatting Formula

    Hi & welcome to MrExcel
    You haven't given us much to go on, but maybe
    =AND($G2<>"",G2=MIN($G2:$M2))
    Last edited by Fluff; Aug 8th, 2019 at 09:22 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting Formula

    Welcome to the forum!

    Use the built-in "Bottom 10..." rule. This ignores blanks and text entries. Set it to Bottom 1 for the minimum value.
    Or use this formula (assuming your range to format is G2:M10): =G2=MIN($G$2:$M$10).

    Helping you to Excel

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting Formula

    Thank you for your reply, sorry if my first post was a little unclear.

    Your solution works, however it does not ignore cells in the range that are blank, these also get highlighted and I want to ignore blanks cells

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Conditional Formatting Formula

    In that case they are not blank, with my formula I get

    GHIJKLM
    20.9040764.099045419610.0263950.1714420.0080130.162466
    34.073879419540.1358460.0074820.368573
    40.999065419470.0355160.2290670.511311
    50.9946993.713642419400.5642440.8699770.888550.751162
    60.9671993.774746419330.3445010.6896010.9540960.586942
    70.9655643.919768419260.0616510.0596920.6862990.248297
    83.9569419190.0866980.0222055.61E-070.294444
    90.9933894.1043250.0304570.0480710.17452
    100.5557073.949629419050.0016780.6440460.992260.040965
    11-0.091994.029621418980.0823060.3690350.286891
    120.8355133.997745418910.1632430.165150.0265450.404033

    Paste



    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting Formula

    Hi Claire

    Thank you for your reply. This worked but like the other suggestion, only in part. It seems like the formula is only being applied to certain cells, as some numbers are being highligted and some are not.

    The reference area I am using is =$G$2:$M$134

    Any help here much appreciated

  7. #7
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting Formula

    Can you supply any further information? Are the cells being calculated by a formula (and if so, what)? What sort of range of values should result? Abd I didn't check whether you want the CF once for the whole block or individually per column?

    Helping you to Excel

  8. #8
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting Formula

    What I did not explain is that the formula needs to work across a row range and not a column range. Each row is a different product, so I want the lowest price in that field to be highlighted and where there is not a price, it ignores the blanks.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Conditional Formatting Formula

    Do the cells contain a formula? if so what is it?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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