Loop Not Completing All Loops

  • Im using the following code to assign numerical values to words in two columns, then multiplying the values together and painting a cell with a specific color assigned to the final number. The problem is it wont do this past row 19, any ideas as to why?

    Suggestions for a better way to do this are VERY WELCOMED!


  • Re: "next Command" Not Going Past Row 19

    Yes i actually found out it was that, i was using a different worksheet to find the last cell. Thank you anyways Andy!

  • 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':
    High 10
    Medium 5
    Low 1

    and this one 'Critical'
    Critical 10
    Serious 8
    Moderate 5
    Minor 1

    then I changed the 'Formula is' formulae to:
    Condtion 1
    Condition 2
    Condition 3

    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.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!