Re: "next Command" Not Going Past Row 19
Yes, put this in the Immediate pane of the VBE and see if it comes back with what you expect..
Results might be a bit more predicatable with
mLastRow = Sheets("TheSheetsName AsOnTheTab").Cells(Rows.Count, "I").End(xlUp).Row
mLastRow = Sheet3.Cells(Rows.Count, "I").End(xlUp).Row
As for a better way to do it? Well here's a different way to do it, no vba:
This is for row 20, you can copy up/down:
In any column in row 20 place this formula:
Note that it refers to I20 and J20. This will give the same result of the multiplication as yours. Copy the formula up/down as required (and yes, you're right, this doesn't automatically find how far down to go).
Next format all those cells with a green background.
Finally in whichever cell you've chosen to put the formula you can add some conditional formatting. See the attached cf1 graphic. Alternatively, to be able to apply the conditional formatting to other cells, for example to colour the cells containing the words themselves see graphic cf2 attached which was looking at the value in cell, but could be applied to any cell, most usefully, in the same row.
Finally, without using any extra cells containing formulae, but requiring 2 small vlookup tables (2 columns each) anywhere in the workbook, even on another sheet, I created these and made them Named ranges, this one I called 'Grade':
and this one 'Critical'
then I changed the 'Formula is' formulae to:
not forgetting to have the default cell background set to green.
The conditional format still refers to the cells $I20 and $J20, which allows the conditional format to be copied and pasted (eg. with the format painter) to any row or column.