Hsieh

¦^´_ 3# tonycho33

¬O§_¦pªþ¥ó®ÄªG
Àx¦s®æ¦Û­qªí³æ.rar (19.18 KB)

¤U¸ü¦¸¼Æ: 24

2012-2-1 23:52

Hsieh

¦^´_ 5# tonycho33

¦p¸¯¬O·j´M2Ä檺¸ê®Æ
¨º´N¦A¼W¥[§PÂ_¦b­þ¤@Äæ·j´M¨ì
¤~¯à¥¿½T«ü¥XCÄæ¥H«áªº¸ê®Æ

Hsieh

¦^´_ 7# tonycho33

Sheet1¼Ò²Õ

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2. Set Rng = Range("B2:E2,B5:E5,B10:E10")
   
3. Rng.Interior.ColorIndex = xlNone
   
4. Rng.Font.ColorIndex = xlAutomatic
   
5.     If Not Intersect(Rng, Target) Is Nothing Then
   
6.     Set a = Sheet2.[A:B].Find(Target, lookat:=xlWhole)
   
7.     If Not a Is Nothing Then
   
8.    k = 3 - a.Column
   
9.     Ar = a.Offset(, k).Resize(, 5).Value
   
10.         With UserForm1
    
11.     .Show 0
    
12.     '¦]¬°TEXTBOX¨Ã¥¼¨Ì§Ç±Æ¦C©Ò¥H¥²¶·¤@¤@µ¹­È
    
13.     .TextBox27 = Ar(1, 4)
    
14.     .TextBox28 = Ar(1, 5)
    
15.     .TextBox29 = Ar(1, 3)
    
16.     .TextBox30 = Ar(1, 2)
    
17.     .TextBox31 = Ar(1, 1)
    
18.     End With
    
19.     End If
    
20.     End If
    
21. End Sub

½Æ»s¥N½X

¤@¯ë¼Ò²Õ

1. Sub Ex()
   
2. Dim Ob As Shape, Ar(), Rng As Range
   
3. Set Ob = Sheet1.Shapes(Application.Caller)
   
4. Set Rng = Sheet1.Range("B2:E2,B5:E5,B10:E10")
   
5. Rng.Interior.ColorIndex = xlNone
   
6. Rng.Font.ColorIndex = xlAutomatic
   
7. a = Asc(Ob.TextFrame.Characters.Text) + 2
   
8. b = Sheet1.Cells(Ob.TopLeftCell.Row, "L").Value
   
9. With Sheet2
   
10. For Each c In .Range(Chr(a) & 1).EntireColumn.SpecialCells(xlCellTypeConstants)
    
11. If c < b Then
    
12. For i = 1 To 2
    
13. If .Cells(c.Row, i) <> "" Then
    
14. ReDim Preserve Ar(s)
    
15. Ar(s) = .Cells(c.Row, i)
    
16. s = s + 1
    
17. End If
    
18. Next
    
19. End If
    
20. Next
    
21. End With
    
22. If s > 0 Then
    
23. For Each d In Ar
    
24.   With Rng.Find(d, lookat:=xlWhole)
    
25.   .Interior.ColorIndex = 3
    
26.   .Font.ColorIndex = 2
    
27.   End With
    
28. Next
    
29. End If
    
30. End Sub

½Æ»s¥N½X