Get row number when macro triggers

[TheWennerWoman]

Hello,

Slightly misleading title but I couldn't think of a better way to put it.

I have the following code:

Private Sub Worksheet_Calculate()
Const x As String = "Rate has changed"
Dim formulaColor As Long
Dim cell As Range

LinkedCells = RGB(Red:=0, Green:=0, Blue:=255)

    For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    If InStr(1, cell.Formula, "changed") > 0 Then
    cell.Font.Color = LinkedCells
    End If

    Next
End Sub

The range in question is C4:J25 and this works perfectly.

What I'd like to do is trap the row number of the cell that has been affected by my code.

Is this possible? Or does the above need to be written slightly differently?

Thank you for reading.

 

[rollis13]

As cell.Font.Color = LinkedCells will assign the font color to your cell, Cell.Row will retrive the cell's row number.

 

[TheWennerWoman]

As cell.Font.Color = LinkedCells will assign the font color to your cell, Cell.Row will retrive the cell's row number.

No, if I do

MsgBox Cell.Row

that loops through every cell in the range and displays a message regardless?

 

[rollis13]

Your MsgBox goes inside the If/Then loop and will show the row number of every cell having the font color changed.
But I probably missed the point, maybe you want to know the address (row) of the cell that triggered the Worksheet_Calculate event.

 

[TheWennerWoman]

Your MsgBox goes inside the If/Then loop and will show the row number of every cell having the font color changed.
But I probably missed the point, maybe you want to know the address (row) of the cell that triggered the Worksheet_Calculate event.

Yes I just need to know exactly that.

From Google it appears that once you enter the realms of Private Sub Worksheet_Calculate() then it triggers regardless of which cell is changed.

I just need to know if a formula in, say, Cx, results in a new value then tell me it was row x.

 

[rollis13]

Yes, Calculate is a global command like F9 or Shift+F9, so with it's Event there is no way to detect a specific cell.