Conditional Formatting Error - incorrect rows are filled

Question

I am trying to apply conditional formatting with 4 criteria:

1. If the cell shows "1" - the row becomes green
2. If the cell shows "2" - the row becomes yellow
3. If the cell shows "3" - the row becomes red
4. If the cell shows "4" - the row becomes grey

What I do:

1)Select the range (table with data) =$A$5:$P$140

2. push condittional formatting - new rule - "use a formula to determine..." - I am typing in the formula $K5="1" and choosing "format" fill "color"

P.S. K - is the row, in which 1/2/3/4 appear

Then I repeat the action 3 more times with only difference in color. 

Green and yellow colors work properly, but as "3" or "4" parameter appears - chaos starts. Everything is mixed, some rows are white, incorrect rows are highleted etc. 

Could you please point out my mistake?

Thanks in advance!

Answer 1

OK, thanks. This is what I see when I create the rules:

Answer 2

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.

Answer 3

Does column K really contain 1, 2, 3 and 4 as text values? If it contains 1 etc. as numbers, the formulas should be

=$K5=1

etc. instead of

=$K5="1"

Answer 4

it contains text, which is to be chosen with "data validation" option. 1/2/3/4 - an example. 

There are 4 Companies' names.

Value is not numerical - it is text.