Conditionally formatting a row

[timmlaww]

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]

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

 

[Hui]

Timmlaww

Note the = sign in front of the $L$160

thats important

 

[timmlaww]

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]

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]

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.