Hsieh

1. Private Sub Workbook_Open()
   
2. Dim sh, fs$, s As Worksheet, mystr$, a(), C As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. fs = ThisWorkbook.Path & "\source.xls" 'source.xls¥Ø¿ý»Pmapping.xls¬Û¦P
   
5. sh = Array("2.2", "3.0")
   
6. With Workbooks.Open(fs)
   
7.    For Each s In .Sheets(sh)
   
8.       With s
   
9.         a = .Range(.[A1], .[A65536].End(xlUp))
   
10.         For i = 1 To UBound(a)
    
11.            mystr = .Name & Right(a(i, 1), 4)
    
12.            d(mystr) = d.Count
    
13.         Next
    
14.       End With
    
15.    Next
    
16. .Close
    
17. End With
    
18. With Me
    
19.    For Each s In .Sheets(sh)
    
20.    s.UsedRange.Interior.ColorIndex = -4142
    
21.       For Each C In s.UsedRange
    
22.          If d.exists(s.Name & C) Then C.Interior.ColorIndex = 6
    
23.       Next
    
24.    Next
    
25. End With
    
26. End Sub

½Æ»s¥N½X

Hsieh

fs$¬O±NÅܼƫŧi¬°¦r¦ê«¬ºA¡A³o¸Ì¬O¥Î¨Ó·í§@source.xlsªº§¹¾ã¥Ø¿ý¦r¦ê
sh¨S¦³«Å§i¥ô¦ó«¬ºA¡A©Ò¥H³QÀq»{«Å§i¬°Variant
¥¿½T¼gªkÀ³¬°Dim sh As Variant
¦Ósh = Array("2.2", "3.0")
´N«ü©wshªº¤º®e¬°¤@ºû°}¦C¡A¨ä¤º®e¬O2­Ó¼Æ­È«¬ªº¦r¦ê¡A¦¹³B´N¬O§Aªº¨Ó·½ÀÉ®×­n¤ñ¹ïªº2­Ó¤u§@ªí¦WºÙ
a()´N¬O«Å§i¤@­Ó°}¦CÅܼÆa¡A¦¹°}¦C¤º®e
a = .Range(.[A1], .[A65536].End(xlUp))
´N¬Osource.xlsªºsheets("2.2")¤Îsheets("3.0")¤u§@ªí¤ºAÄ檺¸ê®Æ½d³òªº­È