Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: conditional formatting question

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i want to highlight lowest value on a range of cells say
    A1:F1

    i dont see an option in the cond formating to do this do i need to type a formula?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-05 10:50, beastwood wrote:
    i want to highlight lowest value on a range of cells say
    A1:F1

    i dont see an option in the cond formating to do this do i need to type a formula?
    Select A1:F1.
    Activate Format|Conditional Formatting.
    Select Formula Is for Condition 1.
    Enter as formula:

    =A1=MIN($A$1:$F$1)

    Activate Format.
    Select a desired color either from "Patterns" tab or "Font' tab.
    Activate OK, OK.

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thx

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When you format the cells using a color for shading, that color displays in the range until you enter values. If your spreadsheet has multiple rows where you apply this formatting, you'll have a large block of cells with color making the spreadsheet unsightly. Is there a way to apply the formatting to the range of cells only when data is present in one or more of the cells?

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 20:40, JimH wrote:
    When you format the cells using a color for shading, that color displays in the range until you enter values. If your spreadsheet has multiple rows where you apply this formatting, you'll have a large block of cells with color making the spreadsheet unsightly. Is there a way to apply the formatting to the range of cells only when data is present in one or more of the cells?
    Hi JimH:
    It is not clear from your question whether you are talking about Conditional Formating or Formating Cell by filling them in with some color.

    Conditional Formating correctly applied would do what you are describing to be a desirable way of doing it.

    Regards!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi,

    I'm talking about conditional formatting. I used Aladin's solution from above, but I modified his formula from =A1=MIN($A$1:$F$1) to =A1=MIN($A1:$F1) because it didn't work when I copied it down several rows.

    All the cells where I copied the conditional format are green until a value is entered into one of the cells. I'd like to have the cells blank (with no color) until all values are entered in A1 to F1 and the minimum value is determined. I hope this is clear.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-09 22:55, JimH wrote:
    Yogi,

    I'm talking about conditional formatting. I used Aladin's solution from above, but I modified his formula from =A1=MIN($A$1:$F$1) to =A1=MIN($A1:$F1) because it didn't work when I copied it down several rows.

    All the cells where I copied the conditional format are green until a value is entered into one of the cells. I'd like to have the cells blank (with no color) until all values are entered in A1 to F1 and the minimum value is determined. I hope this is clear.
    Jim,

    Select the whole range of interest before applying conditional formatting. Lets say that the range is A1:F10.

    Use the following formula to avoid the problem you mention:

    =AND(ISNUMBER(A1),A1=MIN($A$1:$F$10))

    Aladin

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    This isn't working exactly right. I need it to highlight the lowest value in each row not the lowest value in the entire range.

    Thanks,
    Jim

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-05-10 09:46, JimH wrote:
    Aladin,

    This isn't working exactly right. I need it to highlight the lowest value in each row not the lowest value in the entire range.

    Thanks,
    Jim
    Select all the rows of interest via row indicator. And use:

    =AND(ISNUMBER(A1),A1=MIN(1:1))

    I assumed from row 1 on. Adjust to suit.


  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I use the conditional formatting with two conditions:

    1. Condition 1: enter the formula =(isblank(A1)) and select patterns, no color. The cell will remain non-shaded until a value is entered.

    2. Condition 2: enter your other formula with desired color, etc.

Some videos you may like

User Tag List

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
  •