Store Target.Address in a variable

Question

What i am trying to achieve is that a user inputs a value into column B for instance and if its below a limit (say 50) then you look at the date in column A and change the sheet tab colour of that date to red if its below 50.

However i am having issues with putting target.adress into a variable and i keep getting a type mismatch error.

This is what i have currently.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R as integer
dim c as integer
Dim cl As Range
Dim Tb As Range
Dim RG As Range

Set RG = Intersect(Target, Range("B2:B50"))
Set Tb = Target.Address
Set r = Tb.row
Set c = Tb.column - 1
Set cl = cells(R,C).value 
Set cl = Replace(cl, "/", ".")

    If RG Is Nothing Then
    Exit Sub
    ElseIf Target.Value < 50 Then
       cl.Tab.Color = vbRed

        End If
End Sub

The set tb = target.address doesnt seem to work no matter what i do.

Any help is appreciated.

`Target.Address` returns a String, not a Range. Change your Dim and do not use Set. — braX, Dec 17 '19 at 10:35
Since you are using `Set r = Tb.row` i.e tb as range, I recommend keeping it as `Range` and not `String`. You can (whenever you want) get the address using `Tb.address` — Siddharth Rout, Dec 17 '19 at 10:38
Either that or you can always just use `Target.Row` to get the row. Seems like a bit of a waste to make an exact copy of `Target`. — braX, Dec 17 '19 at 10:38
You will get the next error on `Set cl = cells(R,C).value ` ;) I am sure that you have now understood how to fix it? — Siddharth Rout, Dec 17 '19 at 10:46
And while we have you here, you should probably define `r` as a `Long` instead of an `Integer` just in case it ever needs to be larger than 32,767 - It's good practice to always use a `Long` for row variables. — braX, Dec 17 '19 at 10:47

score 1 · Answer 1 · answered Dec 17 '19 at 11:23

This may helps you:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim tb
    Dim Row As Long
    Dim Col As Long

    If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then

        tb = Target.Address
        Row = Target.Row
        Col = Target.Column

    End If


End Sub

Siddharth Rout · Answer 2 · 2019-12-17T11:05:33.993

You have correctly declared the tb object as Range since you are using it as a range later in the code. The problem is that both Target and tb are range and you are trying to store a String to a range.

Change

Set Tb = Target.Address

to

Set Tb = Target

Few other things.

Since you are working with rows, better to use Long instead of Integer to avoid any possible overflow error. Of course you are working with a very small range here so that situation may not arise.
You will get the next error on Set cl = cells(R,C).value ;) I am sure that you have now understood how to fix it?

Btw your code can be reduced to this (Untested). You may want to also read about error handling Here

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long, c As Long

    On Error GoTo Whoa

    Application.EnableEvents = False

        If Not Intersect(Target, Range("B2:B50")) Is Nothing Then
            r = Target.Row
            c = Target.Column - 1
            Cells(r, c).Value = Replace(Cells(r, c).Value, "/", ".")

            If Target.Value < 50 Then ActiveSheet.Tab.Color = vbRed
        End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

@braX: This is what happens when you post without testing :( which i rarely do... — Siddharth Rout, Dec 17 '19 at 11:01

Store Target.Address in a variable

2 Answers2