+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting for biggest number that is smaller than cell value.

  1. 06-07-2017, 03:48 AM #1
    jp16
    jp16 is offline
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Conditional Formatting for biggest number that is smaller than cell value.

    Hi, i have a value in certain cell. Eg A1. I have a row of number from C1:Z1, i wan the biggest number which is smaller than A1 to have a Green Font. HOw should i do? See attached for example
    Reply With QuoteRegister To Reply
  2. 06-07-2017, 04:03 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Conditional Formatting for biggest number that is smaller than cell value.

    You didnt attach the file?
    I have a row of number from C1:Z1
    Did you mean column?
    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
    Reply With QuoteRegister To Reply
  3. 06-07-2017, 04:11 AM #3
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Conditional Formatting for biggest number that is smaller than cell value.

    Try in conditional formatting rule below formula

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    1
    34
    26
    34
    15
    39
    41
    31
    42
    23
    47
    37
    42
    28
    18
    16
    47
    37
    29
    15
    19
    23
    39
    21
    26
    12


    Check the attached file.
    Attached Files Attached Files
    Last edited by shukla.ankur281190; 06-07-2017 at 04:13 AM. Reason: Attachment added
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  4. 06-07-2017, 04:29 AM #4
    jp16
    jp16 is offline
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Conditional Formatting for biggest number that is smaller than cell value.

    Quote Originally Posted by shukla.ankur281190 View Post
    Try in conditional formatting rule below formula

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    1
    34
    26
    34
    15
    39
    41
    31
    42
    23
    47
    37
    42
    28
    18
    16
    47
    37
    29
    15
    19
    23
    39
    21
    26
    12


    Check the attached file.
    Hi, your formula work. BUt how do i copy n paste the conditional formatting down the row? I have many rows that need this conditional formatting
    Reply With QuoteRegister To Reply
  5. 06-07-2017, 04:31 AM #5
    jp16
    jp16 is offline
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Conditional Formatting for biggest number that is smaller than cell value.

    Quote Originally Posted by FDibbins View Post
    You didnt attach the file?

    Did you mean column?
    Sorry. Please refer attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 06-07-2017, 04:34 AM #6
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Conditional Formatting for biggest number that is smaller than cell value.

    Quote Originally Posted by jp16 View Post
    Hi, your formula work. BUt how do i copy n paste the conditional formatting down the row? I have many rows that need this conditional formatting
    Try

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 06-07-2017, 05:53 AM #7
    jp16
    jp16 is offline
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Re: Conditional Formatting for biggest number that is smaller than cell value.

    Quote Originally Posted by shukla.ankur281190 View Post
    Try

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Can you explain y there is a =C1 behind? LARGE(IF($C1:$Z1<$A1,$C1:$Z1),1) mean if C1 to Z1 is bigger than A1, then take the value right? the 1 mean the 1st biggest right? But y is there a =C1?
    Reply With QuoteRegister To Reply
  8. 06-07-2017, 08:22 AM #8
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,937

    Re: Conditional Formatting for biggest number that is smaller than cell value.

    Large function will determine the Large value in C1:Z1 range and that particular value must be equal to that range and whenever the same match it will make true otherwise all the checking give false.

    Hope this help you
    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. [SOLVED] Conditional formatting if cell content a number
    By jackson_hollon in forum Excel General
    Replies: 3
    Last Post: 12-07-2014, 03:47 PM
  2. VBA To find nearest biggest number and lowest number
    By gan_xl in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2014, 05:49 AM
  3. [SOLVED] Compare number and color cell if biger or smaller
    By besmir in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-28-2013, 08:12 AM
  4. [SOLVED] how do i make one cell change colour when the number is greater or smaller than another
    By Sheepkin_Coat in forum Excel General
    Replies: 5
    Last Post: 07-25-2013, 08:24 AM
  5. Conditional Formatting to show biggest reductions in %
    By LDouble3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2013, 11:45 AM
  6. Conditional Formatting where text in another cell translate to a number
    By tstaller in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2012, 12:52 PM
  7. Conditional formatting row when cell value is an even number
    By hhunt in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-02-2005, 06: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1