Unsupported object or method...
 

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


--
Message posted from http://www.ExcelForum.com

Unsupported object or method...
 

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

--
Regards,
Tom Ogilvy

"mjack003 " wrote in message
...
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 a
number of grouped worksheets on a master sheet by corresponding numbers
in column A. Any help of why the cell formatting won't work is
appreciated. I use XL 2000 if that helps.

Best Regards,
Mjack



---
Message posted from http://www.ExcelForum.com/

Unsupported object or method...
 

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

--
Message posted from http://www.ExcelForum.com