Conditional Formatting Highlighting

I have a cell that is conditionally formatted under "Use a formula to determine which cells to format" with the following formula: =SUBSTITUTE(M1," ","" )= P4 & Q1
What happens is that data comes into M1 and if that data is equal to P4 & Q1 then the cell is highlighted. This works good. My problem is that I need the row in which the highlighting takes place to stay highlighted. Right now, if the data changes in M1 the highlighting moves to the other cells that are formatted and unhighlights the previous cell. Also, I tried copying the formatting to all the other cells, but the formula in the conditional formatting changes the M1 to M2 then M3 as it goes down the column. I would like to keep it M1. I have attached the sample worksheet for viewing. Thanks.

Hi John,

Try this Code instead

"Code: =SUBSTITUTE($M$1," ","" )= P4 & Q1"

Regards,

Veejar

Good, that worked. How about this. Instead of looking at just P4, can I change the formula to include the whole column? Thanks.

Good to go.