Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: formatting based on a condition

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

    Default

    Can anyone give me an example of how to format a ROW based on a formula? For example - if cell E6= "FL" then shade the entire row 6 Green.

    I've seen examples on formatting text, but not entire rows.

    Thanks

    Paul

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    1. Select the entire row
    2. Choose the Format | Conditional Formatting... menu command
    3. Setup a "Formula Is" condition using =$E6="FL" and applying a green cell pattern.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Howdy,

    Try the Tip Of The Week on the Mr Excel homepage. Underneath the Gantt part, it shows you what you want to know

    Audiojoe
    Sometimes I feel like I don't have a partner

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-22 07:22, paulrus wrote:
    Can anyone give me an example of how to format a ROW based on a formula? For example - if cell E6= "FL" then shade the entire row 6 Green.
    1. Highlight row 6
    2. Select conditional format from the format menu
    3. change drop down to formula.
    4. enter formula
    =$e$6="fl"
    5. Click format box and choose your green

    good luck


    [ This Message was edited by: IML on 2002-04-22 07:31 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm sorry - my question wasn't specific enough. What I wanted to do was conditionally format it not only based on whether or not E6= "FL", but I want to evaluate all of E:E and format rows based on whether or not the state listed is FL, HI, MS, NJ, and so on. Basically I have a list of states, if E:E = one of the names on that list I want to change the color of the row so it's easy for me to spot those rows.

    I'm still learning so forgive me for the newbie questions.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you could name your list "states" via the name box.

    following the conditional formmating steps and use the formula
    =COUNTIF(states,A6)

    in cell a6.

    Select a6, use the format painter and then select all of row 6.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Make that...

    =COUNTIF(states,$E6)

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well.. this isn't working, but here's what I was trying:

    =COUNTIF(states({"FL", "HI", "MS", "NJ", "SC", "DE", "KY", "LA", "MA", "NM", "PR", "VT", "WA", "WV", "WI"}),4:52)

    Basically between rows 4:52 if states is one of those listed I wanted to change the color of the row.

    This is a bit frustrating....

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-22 08:02, paulrus wrote:
    Well.. this isn't working, but here's what I was trying:

    =COUNTIF(states({"FL", "HI", "MS", "NJ", "SC", "DE", "KY", "LA", "MA", "NM", "PR", "VT", "WA", "WV", "WI"}),4:52)

    Basically between rows 4:52 if states is one of those listed I wanted to change the color of the row.

    This is a bit frustrating....
    You can't use an array constant for Conditional Formatting or Data Validation. List your states in a cell range, and use...

    =COUNTIF(cell_range,$E6)

    ...where cell_range is the cell range containing your state abbreviations.

    [ This Message was edited by: Mark W. on 2002-04-22 08:07 ]

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok.. I'm getting it...



    [ This Message was edited by: paulrus on 2002-04-22 08:12 ]

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
  •