Conditional Formatting Highlighting Incorrect Rows

[mawatts25]

Ok. Not an excel master here but am somewhat familiar with formulas. Admittedly, I have never worked with conditional formatting before so most of this has been trial and error and google. I have a database with multiple columns of data. I need to highlight entire rows based on the presence or absence of data in a certain cell in that row. All of these rules need to apply from row 2 downward- row 1 is reserved for headers and should not be analyzed by the rules.

For example, if K2 has data in it (IE Not blank) I need all of row 2 highlighted. I have 4 total rules I have attempted to apply to the entire document, listed below.

However, everything highlights the row above, not the row I need. For example, if K2 has data in it (IE Not blank) I need all of row 2 highlighted, yet it highlights row 1.

Rules (all are formatted to highlight the row red, and all apply to $A:$N):
=NOT($J2="") meant to highlight an entire row if the J cell in that row contains data of any form
=NOT($JK="") meant to highlight an entire row if the K cell in that row contains data of any form
=I2="" meant to highlight an entire row if the I cell in that row is blank
=COUNTIF($A:$N,$A)>1 meant to highlight entire rows if duplicate data exists in the A column (IE rows 2 and 65 get highlighted because they have an identical A value)

Any help would be highly appreciated.
-Mike

 

[Joe4]

Welcome to the Board!

It sounds like you are one row off when trying to apply your CF formulas.
Note that when trying to apply Conditional Formatting formulas across multiple cell ranges, after selecting your range, you need to write the formula as it pertains to the very FIRST cell in your selection.
So, if you are selecting entire columns, you would write the formula to reference row 1, not row 2.

If you want to exclude row 1 from your CF rules, then you need to do one of the following:
- NOT include row 1 in your selection (meaning you cannot select entire columns)
- amend your formula to exclude row 1.

So, if you selected entire columns, and wanted to check for the value in column K not blank, but exclude row 1, you could use a formula like this:

=AND($K1<>"",ROW()>1)

 

[mawatts25]

Thank you SO MUCH Joe4!

 

[Joe4]

You are welcome!

 

[mawatts25]

One more question please:

=AND(LEFT($O1,4)="GOOD",ROW()>1)

Trying to highlight any row in which Good- XXXXXXX appears in a cell in column O. Mutiple values could be "Good- Room checked" "Good- Checkout" Etc. The formula must also skip row 1 as it is headers.

Suggestions?

 

[Joe4]

Suggestions?

Yes, the formula you posted looks absolutely correct and works fine for me.

If it is not working for you, make sure of the following:
- Your selected range starts with row 1
- You do not have any spaces or anything else in front of the word "GOOD"
- You do not have any other Conditional Formatting rules on the same cells which may be taking precedence over this rule.

 

[Peter_SSs]

If you want to exclude row 1 from your CF rules, then you need to do one of the following:
- NOT include row 1 in your selection (meaning you cannot select entire columns)
- amend your formula to exclude row 1.

If selecting whole column(s), yet another fairly simple option would be to

- Write your formula to apply to the first cell (in row 1) as normal (this keeps your formula simpler), then once you have completed applying the CF, select row 1 and on the Home ribbon -> Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells

Having said that, I would avoid applying CF to whole columns as it may adversely affect sheet performance.