- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
4#
發表於 2011-5-7 19:46
| 只看該作者
本帖最後由 GBKEE 於 2011-5-7 19:52 編輯
原先看不懂樓主的意思 依樣畫葫蘆 改用 Application.Match 比對- Sub Ex()
- Dim Fs$, Sh As Worksheet, R As Range, AR()
- Fs = ThisWorkbook.Path & "\source.xls"
- ReDim AR(0)
- With Workbooks.Open(Fs)
- For Each Sh In .Sheets
- For Each R In Sh.UsedRange
- If IsError(Application.Match(Right(R, 4), AR, 0)) And R <> "" Then
- AR(UBound(AR)) = Right(R, 4)
- ReDim Preserve AR(UBound(AR) + 1)
- End If
- Next
- Next
- .Close 0
- End With
- With Me
- For Each Sh In .Sheets
- Sh.UsedRange.Interior.ColorIndex = -4142
- For Each R In Sh.UsedRange
- If IsError(Application.Match(Right(R, 4), AR, 0)) = False And R <> "" Then
- R.Interior.ColorIndex = 6
- End If
- Next
- Next
- End With
- End Sub
複製代碼 |
|