Setting Range based on LastCell

[matt4003]

Hello Again,

I am updating some code that allows me to ask users when they change a cell if they "are sure they want to make the change". Current code is posted below for your reference. I am interested in figuring out how to make a static range actually based on the LastCell.

See in the below code the rng is = to A8:BB50000. I would like it to be set for A8:LastCell

Set rng = Intersect(Me.Range("A8:BB5000"), Target)

Below is the code in it's entirety.
Thanks for your help!

Cheers,
Matt

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim res As Long
Dim oldVal As Variant
Dim newVal As Variant
Dim sAdd As String

If Target.Count > 1 Then Exit Sub

On Error GoTo XIT

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set rng = Intersect(Me.Range("A7:BB5000"), Target)

If Not rng Is Nothing Then
sAdd = ActiveCell.Address
newVal = Target.Formula
Application.Undo
oldVal = rng.Formula

With rng

If Not IsEmpty(.Value) And newVal <> oldVal Then
res = MsgBox( _
Prompt:="Are you sure you want " & _
"to change the value of " & _
"Cell " & rng.Address(0, 0) & "?", _
Buttons:=vbYesNo)

If res = vbNo Then
.Formula = oldVal
'.Font = oldFont
Else
.Formula = newVal
.Font.Bold = True
.Font.ColorIndex = 51
End If
Else
.Formula = newVal
.Font.ColorIndex = 51
End If
End With

End If

If Not res = vbNo Then Me.Range(sAdd).Activate

XIT:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With

'With Target
'.Font.Bold = True
'.Font.ColorIndex = 51
'End With

End Sub

[royUK]

Try this

Set rng = Intersect(Me.Range(Cells(7, 1), Cells(Rows.Count, 55)), Target)

[matt4003]

Hello RoyUK,

Can you explain how this works?

Thanks,
Matt

[matt4003]

I can't get it to work...in fact now none of the original code works, even when I go back to the first static selection.

Weird.

[royUK]

The range is set to A1 to the last cell in BB

[matt4003]

Hello,

I don't know what the last cell is, or the last column, I need it to fingure that part out.

Perhaps I need to reset the last cell. I am pretty new to this. I just know I want my original code to be a little more dynamic so it doesn't try to execute for every cell changed...only those A8:LastRow and Row7:LastColumn. That would be the way to find the last cell, right??

Regards,
Matt

[royUK]

You had the range hard coded as "A7:BB5000", that's why I used that column. maybe
using UsedRange would be best.

Set rng = Intersect(Me.UsedRange, Target)