Apply conditional formatting from a cell to a range

An equipment status worksheet

If the status is "SHOP" I need to highlight the cells in four columns that are blank. That prompts the user to put in the missing information.

I created the rule based on this formula

Please Login or Register  to view this content.

And voila the blank cell is hot pink

Problem is that if I type into M2 I want the cell to return to the default white color and I need to copy that rule to the other columns.

I tried using the format painter on the remaining cells and it didn't work.

For some reason Conditional Formatting messes with my head.

What have I done wrong?

Originally Posted by FDibbins

MAybe just...
=and(k2="shop",m2<>"")

1st, highlight the entire range you need this apply to

Originally Posted by AliGW

Don't use the format painter to apply conditional formatting - it can be hit and miss. Instead, use the dialog itself to tell Excel which range or ranges to apply it to. It's not as intuitive as it could be.

I appreciate both of you taking the time to help me. I guess that format painter is like the rest of us, imperfect ;-)

After reading y'alls comment I began to wonder why I needed to get it done with one rule. That led to the solution for my situation. I put the pic here so that down the road if someone has a similar issue it might help them.

Conditional.PNG

Yep. I saw that after posting the pic. I fixed the ranges so they match and are not the entire column.