+ Reply to Thread
Results 1 to 5 of 5

Conditional formatting for an entire row based on other rows (2007)

  1. #1
    Registered User
    Join Date
    11-24-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Conditional formatting for an entire row based on other rows (2007)

    Hi there, I'm having an issue with a little project I'm working on. I need to highlight certain rows based on the values in one particular column -- the tricky part being that it's based on values in the previous and next column. More specifically, I've got numerical values in column W, and I need to highlight each row where the value in column W is greater than or equal to 0.3, OR the values in the two surrounding rows (same column) are equal to or greater than 0.3. I've got part of it -- I made it so that it highlights the row the value in that row or the next one meet the criteria, but I don't know how to reference the previous row. Here's what I have so far (under "use a formula to determine what cells to format):

    =OR($W1>=0.3,$W2>=0.3)

    Since W0 isn't a row, how do I reference the previous row?

    Thanks!

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional formatting for an entire row based on other rows (2007)

    If you must start at row 1, then apply separate CF to row 1..

    e.g. =or($W1>=3,$W2>=3)

    and for row 2 onwards

    =or($W2>=3,$W1>=3,$W3>=3)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-24-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Conditional formatting for an entire row based on other rows (2007)

    My understanding is that, if I want to highlight an entire row, I have to select the whole sheet and use the first cell in the column as my reference cell (W1). Also, the criteria state that the previous AND next values must be greater than or equal to 0.3, so this is what I'm trying to do:

    =OR($W1>=0.3,AND(W2>=0.3,W0>=0.3))

    However, cell W0 doesn't exist so I'm getting an error.

    Thanks.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional formatting for an entire row based on other rows (2007)

    Quote Originally Posted by Krushr View Post
    My understanding is that, if I want to highlight an entire row, I have to select the whole sheet and use the first cell in the column as my reference cell (W1).
    No, not true, you can select from any row down, but your formula needs to reference the top-most row you selected.

    So if you select row 1 and apply:

    =OR($W1>=0.3,W2>=0.3)

    and then select Row 2 down and apply:

    =OR($W2>=0.3,AND(W3>=0.3,W1>=0.3))

  5. #5
    Registered User
    Join Date
    11-24-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Conditional formatting for an entire row based on other rows (2007)

    Great, thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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