VBA based conditional formatting (>3 formats needed)

Good Morning/Afternoon/Evening

I'm looking to conditionally format the cell colour of an entire row based on text entered in the last cell of the row. Excel's built-in conditional formatting is beautiful for this but I need six different formats (and three seems to be the max).

I did a search and found this helpful thread and I'm trying to adapt the code to my needs.

Here's what I've got so far.

Please Login or Register  to view this content.

At one point in time, when I had set RowRng to a static range, the macro would successfully change the row colour whenever I changed a cell in J1:J100.

I then set out to modify my code to change only the row of the selected cell in the J column and came up with this part.

Please Login or Register  to view this content.

I was getting an error at this line, but now all of a sudden for some reason, the macro isn't even getting executed when I enter text in the J column of the workseet. I know this because it isn't showing my message box when I change a value in the J column.

What gives??

Mudraker: That works! Cheers mate.

Roy: I left out some other formats for the sake of shortening the code to pasted here. I'm actually using 7 different formats.

I fixed the quotes and it was still throwing errors so I poked around in the debugger and realized Rng was null until inside the for loop. So I moved that line of code. Here's the code that works for me.

Please Login or Register  to view this content.