How to use Range function with variables?

Anjana - Feb 17, 2016 at 01:48 AM

I need help in running a macro with VB Script. I am trying to select a range with the help of a variable. I have comup with the below code.

Dim sheet1id, sheet2id As String

Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range
Dim Count1 As Integer, Count2 As Integer
Dim i As Integer, im As Integer, a As Integer, b As Integer, c As Integer, J As Integer
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
sheet1id = TextBox1.Text
sheet2id = TextBox2.Text
Set rng1 = ws1.Range(sheet1id).CurrentRegion
Set rng2= ws2.Range(sheet2id).CurrentRegion
Count1 = rng1.Rows.Count
Count2 = rng2.Rows.Count
im = 2
For i = 2 To Count1
im = 2
For im = 2 To Count2
If (rng1.Value(sheet1id) = rng2.Value(sheet2id)) Then
rng1.Cells(i, 1).Interior.ColorIndex = 4
rng1.Cells(i, 2).Interior.ColorIndex = 2
End If

I am getting stuck @ the line If (rng1.Value(sheet1id) = rng2.Value(sheet2id)) Then

Type mismatch error. Any help on this will be highly appreciated :)
Thanks in advance.