Loop code

[joacro]

Hi there,

Wonder if anyone can help me with looping the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("I").Value = 90000 Then
If Range("G").Value >= 90000 Then Range("K").Value = "Gold"
If Range("G").Value < 90000 And Range("G").Value >= 70000 Then Range("K").Value = "Silver"
If Range("G").Value < 70000 And Range("G").Value >= 45000 Then Range("K").Value = "Bronze"
If Range("G").Value < 45000 Then Range("K").Value = "Blue"
ElseIf Range("I").Value = 120000 Then
If Range("G").Value >= 120000 Then Range("K").Value = "Gold"
If Range("G").Value < 120000 And Range("G").Value >= 90000 Then Range("K").Value = "Silver"
If Range("G").Value < 90000 And Range("G").Value >= 70000 Then Range("K").Value = "Bronze"
If Range("G").Value < 70000 Then Range("K").Value = "Blue"
End If
End Sub

I need the code to work for all the cells in G, I and K

Regards

Olivia

 

[Richard Schollar]

Hi Olivia

Do you want to restrict the code to operate only on cells in the selected row? You don't, for example, want the code to iterate through every single row when the selection is changed? So if you were to select G6 say, then the code would just operate on row 6 (thus amending the value in K6 if applicable)?

 

[joacro]

Hi there,

Yes this is what I want to do.

My current code is like this:
If Range("I21").Value = 90000 Then
If Range("G21").Value >= 90000 Then Range("K21").Value = "Gold"
If Range("G21").Value < 90000 And Range("G21").Value >= 70000 Then Range("K21").Value = "Silver"
If Range("G21").Value < 70000 And Range("G21").Value >= 45000 Then Range("K21").Value = "Bronze"
If Range("G21").Value < 45000 Then Range("K21").Value = "Blue"
ElseIf Range("I21").Value = 120000 Then
If Range("G21").Value >= 120000 Then Range("K21").Value = "Gold"
If Range("G21").Value < 120000 And Range("G21").Value >= 90000 Then Range("K21").Value = "Silver"
If Range("G21").Value < 90000 And Range("G21").Value >= 70000 Then Range("K21").Value = "Bronze"
If Range("G21").Value < 70000 Then Range("K21").Value = "Blue"
End If

I would like the code to look for the whole sheet

 

[cstimart]

Try this...

Sub looping_code()
Dim LR As Long
Dim i As Long

LR = Range("G" & Rows.Count).End(xlUp).Row

For i = 1 To LR
    If Range("I" & i) = 90000 Then
        If Range("G" & i) >= 90000 Then
            Range("K" & i) = "Gold"
        ElseIf Range("G" & i) >= 70000 Then
            Range("K" & i) = "Silver"
        ElseIf Range("G" & i) >= 45000 Then
            Range("K" & i) = "Bronze"
        Else
            Range("K" & i) = "Blue"
        End If
    ElseIf Range("I" & i) = 120000 Then
        If Range("G" & i) >= 120000 Then
            Range("K" & i) = "Gold"
        ElseIf Range("G" & i) >= 90000 Then
            Range("K" & i) = "Silver"
        ElseIf Range("G" & i) >= 70000 Then
            Range("K" & i) = "Bronze"
        Else
            Range("K" & i) = "Blue"
        End If
    End If
Next i
End Sub

 

[cstimart]

or maybe this...put in Cell K5 and copy up/down

=IF($I5=90000,IF($G5>=90000,"Gold",IF($G5>=70000,"Silver",IF($G5>=45000,"Bronze",2))),IF($I5=120000,IF($I5>=120000,"Gold",IF($I5>=90000,"Silver",IF($I5>=70000,"Bronze",2))),0))

 

[joacro]

Hi there,

Your code work just perfectly.

I change the sub looping to sub selectionchange

Thank you so much