Selection Change Event, Set New Dynamic Ranges and Loop

  • I have a worksheet selection change event that sets the values in column A as such:

    Sheets("Investigation Grid").Range("A2").Value = 1
    LastRow2 = Range("B" & Rows.Count).End(xlUp).Row
    Sheets("Investigation Grid").Range("A3:A" & LastRow2).FormulaR1C1 = "=IF(RC[7]=R[-1]C[7],R[-1]C,R[-1]C+1)"

    So if the value in column H matches the value in column H for the row above it, then the index number in column A is the same as the index number in column A in the row above, if the value in column H does not match the value in column H in the row above, then the index number in column A is +1 of the index number in row above it.
    This is a way for me to group rows that belong to the same case number found in column H.

    I am trying to add to my worksheet selection change event to look at each index number as its own range… then look at the values in column 22 of that index number range.
    For instance, there are 4 rows that have index number 78.
    Within those 4 rows, I want to look at column 22 and if any of the values in the 4 rows in column 22 say “Substantiated” or “Indicated” then all 4 rows in column 41 should return “Substantiated” or “Indicated” based on whichever value is found in column 22. If none of the column 22 values are “Substantiated” or “Indicated” then the values in Column 41 should be the same as the values in column 22.

    I have updated this to include code i started working on. The ElseIf section is where i need assistance! Thank you !

Participate now!

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