Unsupported object or method...

  • Thread starter Thread starter mjack003
  • Start date Start date
M

mjack003

Hi,

I've been trying to get this macro to work:

Sub Macro3()

Dim masv()
Dim rw As Long, v, i As Long, rng As Range
Dim sh As Worksheet, cell As Range
ReDim masv(1 To (Worksheets.Count - 1) * 484)
rw = 1
For Each sh In Worksheets
If LCase(sh.Name) <> "teardown inventory" Then
Set rng = sh.Range("A17:A500")
v = rng.Value
For i = LBound(v, 1) To UBound(v, 1)
If Len(Trim(v(i, 1))) <> 0 Then
masv(rw) = LCase(v(i, 1))
rw = rw + 1
End If
Next
End If
Next
ReDim Preserve masv(1 To rw - 1)
Set sh = Worksheets("Teardown Inventory")
For Each cell In sh.Range("A6:A4500")
For i = LBound(masv) To UBound(masv)
If LCase(cell.Value) = masv(i) Then
cell.Resize(1,6).ColorIndex = 6
Exit For
End If
Next
Next

End Sub

...but for some reason I keep getting an error of the cell.Resize line
What it is supposed to do is highlight all matches it finds on
number of grouped worksheets on a master sheet by corresponding number
in column A. Any help of why the cell formatting won't work i
appreciated. I use XL 2000 if that helps.

Best Regards,
Mjack
 
This worked for me

changed that line to

cell.Resize(1, 6).Interior.ColorIndex = 6



Sub Macro3()

Dim masv()
Dim rw As Long, v, i As Long, rng As Range
Dim sh As Worksheet, cell As Range
ReDim masv(1 To (Worksheets.Count - 1) * 484)
rw = 1
For Each sh In Worksheets
If LCase(sh.Name) <> "teardown inventory" Then
Set rng = sh.Range("A17:A500")
v = rng.Value
For i = LBound(v, 1) To UBound(v, 1)
If Len(Trim(v(i, 1))) <> 0 Then
masv(rw) = LCase(v(i, 1))
rw = rw + 1
End If
Next
End If
Next
ReDim Preserve masv(1 To rw - 1)
Set sh = Worksheets("Teardown Inventory")
For Each cell In sh.Range("A6:A4500")
For i = LBound(masv) To UBound(masv)
If LCase(cell.Value) = masv(i) Then
cell.Resize(1, 6).Interior.ColorIndex = 6
Exit For
End If
Next
Next

End Sub
 
I replaced that line and now it works a little too well. It highlighte
all items on the master sheet instead of just the ones from th
selected sheets. This is beyond me.
Thanks again.

Regards,
Mjac
 
Back
Top