• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditionally formatting a row

timmlaww

New Member
I need to find a way to conditionally format a range in a row based on the value of a single cell in that row.


EX: If L160="CLOSED", I'd like to get the whole range of cells to be green colored, and if L160="OPEN".


I just can't seem to work it out, any help is appreciated.


Thank you,

timmlaww
 

Hui

Excel Ninja
Staff member
Timmlaww

Select the Row or Rows you want to format

Goto conditional formatting

In the Conditional Formatting dialogue add 2 entries as follows

Condition 1. Formula is =$L$160="OPEN" and set what ever format you want

Condition 2. Formula is =$L$160="CLOSED" and set what ever format you want


Note that the formatting is case sensative


Thats it
 

timmlaww

New Member
Hui,

Thanks, but I guess I wasn't as clear as I should've been...I can't get it to do it for more than a single row. Basically I need to highlight any row in my spreadsheet that has the words "OPEN" or "CLOSED" in column L of that row.


I know it must be something I'm doing wrong with the L column ref. I selected rows 1 through 500 and tried

Condition 1. Formula is =$L$1:$L$5000="OPEN"

Condition 1. Formula is =$L$1:$L$5000="CLOSED"

But it isn't working, I get the standard "The formula you typed contains an error" message.


Maybe there's a better way to get what I need, all I really want to do is find, and move all rows containing the word CLOSED in column L. I'm trying to clean up a large "issue" log, and I want to move the status CLOSED issues to a separate sheet. Any help is appreciated.


Thank you,

timmlaww
 

Hui

Excel Ninja
Staff member
Timmlaww

Try this

Select the Row or Rows you want to format

Goto conditional formatting

In the Conditional Formatting dialogue add 2 entries as follows

Condition 1. Formula is =$L160="OPEN" and set what ever format you want

Condition 2. Formula is =$L160="CLOSED" and set what ever format you want
 

keymaster

New Member
timmlaww : see the examples here: http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/ and follow the guidelines. You need to understand how the relative and absolute references work. A $ placed before L makes it absolute, thus excel checks the same column whether it applies conditional formatting to values in L or K or J or A.
 
Top