better control of a change event

[moneybydallas]

Hello,

I am working on a macro and I have it doing everything I want it to do except one thing. When a change event happens, it continues the loop to the last cell on the worksheet.
I am trying to make a calculation happen and the field change color when a a cell is changed, which it does but, it takes me to the end. This makes me have to click back to change the next one. Here is at least part of the code.


Private Sub Worksheet_Change(ByVal target As Range)

Application.EnableEvents = False

lastrow = Cells(Rows.Count, 1).End(xlUp).row


For RowNum = 2 To lastrow

InsertPrice1 = Cells(RowNum, 5)
If InsertPrice1 = "" Then
Cells(RowNum, 6) = "N/A"


Cells(RowNum, 6).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With

GoTo SkipSection1
End If

Averagecost = Cells(RowNum, 2)
NewMargin1 = Round((InsertPrice1 - Averagecost) / InsertPrice1 * 100, 2)
Cells(RowNum, 6) = NewMargin1

Cells(RowNum, 6).Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With


SkipSection1:



Next RowNum

<strike></strike>


This code repeats for 7 more columns. Then ends with
Application.EnableEvents = True

End Sub


How do I get it to only complete this on the cells that are changed without running through the whole sheet every time?

 

[Norie]

I'm not sure what you are trying to do but the argument Target that is passed to the code is a reference to the range that has been changed.

So perhaps you should work with that rather than looping through a range that seems to be kind of unrelated to it.