Not all selected values formatting with excel conditional formatting

Question

So i'm doing some conditional formatting. I want a row of values to turn green when a cell has the string "paid" present in it.

So far I've been able to make the conditional formatting work by selecting my entire range of data for the "Applies To" field, and then removing the "$" from my formatting rule which is N2 = "paid".

So I have:

Applies to: $A$2:$N$106 and Rule: =N2 = "paid"

However, when i do this only the first column (A) in my selected range turns green, not the entire row like i was wanting.

If i leave the "$" in my rule then the entire row is formatted, but then the rule doesn't account for the changing value in the N column.

I've tried using the format painter and everything, but i can't find a solution.

Cheers

Answer 1

If you want the entire row grenn based on what is in column N then 'lock' column N as an absolute reference.

With A2:N106 selected,

 =$N2="paid"

Without the $ to lock column N, column A references =N2="paid" but column B references =O2="paid", column C references =P2="paid", etc.

Answer 2

Highlight $A$2:$N$106 range then from conditional Formatting menu select "Conditional Formatting" => "Highlight Cells Rules" => "Equal To"

Place the following formula: =IF($N2="paid",A2,"Something That Doesn't Match")

In this case if any cell in column N has value "paid" each cell within that row would equal to it's value and satisfy conditional formatting rule.

I have tested - it works.

Answer 3

It can be done in two ways,

1st: You can give the cell reference like this

2nd: You can type the string itself in the formula bar