GBKEE

¦^´_ 1# »k¥ú°j¯T
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Private Sub CommandButton1_Click()
   
3.     Dim Rng(1 To 2) As Range
   
4.     Set Rng(1) = Range("c7", Range("c" & Rows.Count).End(xlUp))
   
5.     If Rng(1).Cells(1) = Range("c6") Then MsgBox "¤À¼ÆÄæ ¨S¦³¸ê®Æ": Exit Sub
   
6.    
   
7.     With Range("A6").Resize(Rng(1).Rows.Count + 1, 7)
   
8.         .Interior.ColorIndex = xlNone
   
9.         .Sort Key1:=Range("C6"), Order1:=xlDescending, Header:=xlYes  '¦Û°Ê±Æ¦C¤j¤p(»¼´î±Æ§Ç)
   
10.     End With
    
11.     For Each E In Rng(1)
    
12.                         'AÄ椽¦¡
    
13.         E.Cells(1, -1) = "=IF(RC[2]="""","""",LOOKUP(RC[2],{0,701,1301,1501,1701},{""B"",""A"",""S"",""S+"",""SS""}))"
    
14.         E.Cells(1, -1) = E.Cells(1, -1).Value
    
15.                         'BÄ椽¦¡
    
16.         E.Cells(1, 0) = "=IF(RC[1]="""","""",RANK(RC[1],C[1]))"
    
17.         E.Cells(1, 0) = E.Cells(1, 0).Value
    
18.         Set Rng(2) = Range("H:H").Find(E.Cells(1, -1), LOOKAT:=xlWhole)
    
19.         E.Cells(1, -1).Interior.ColorIndex = Rng(2).Interior.ColorIndex
    
20.         E.Cells(1, 5).Interior.ColorIndex = Rng(2).Interior.ColorIndex
    
21.     Next
    
22. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 3# »k¥ú°j¯T

1. For Each E In Rng(1)
   
2.                         'AÄ椽¦¡
   
3.         E.Cells(1, -1) = "=IF(RC[2]="""","""",LOOKUP(RC[2],{0,701,1301,1501,1701},{""B"",""A"",""S"",""S+"",""SS""}))"
   
4.         E.Cells(1, -1) = E.Cells(1, -1).Value
   
5.                         'BÄ椽¦¡
   
6.         E.Cells(1, 0) = "=IF(RC[1]="""","""",RANK(RC[1],C[1]))"
   
7.         E.Cells(1, 0) = E.Cells(1, 0).Value
   
8.         '******** AÄ椽¦¡ªº­È ¦³¦b"±¡³ø°Ï" ¶Ü? ************************
   
9.         '¤£¦b·|¦³¡i°õ¦æ¶¥¿ù»~'91'¡j ªº¿ù»~¬O¡i¨S¦³³]©wª«¥óÅÜÅÜ©ÎWith °Ï¶ôÅܼơj
   
10.       '********************************************************
    
11.         Set Rng(2) = Range("H:H").Find(E.Cells(1, -1), LOOKAT:=xlWhole)
    
12.         If Not Rng(2) Is Nothing Then  '±ø¥ó¦¡§PÂ_: AÄ椽¦¡ªº­È ¦b"±¡³ø°Ï" ¦³§ä¨ì.±Æ°£AÄ椽¦¡ªº­È ¤£¦b"±¡³ø°Ï"ªº¿ù»~
    
13.             E.Cells(1, -1).Interior.ColorIndex = Rng(2).Interior.ColorIndex            
    
14.             E.Cells(1, 5).Interior.ColorIndex = Rng(2).Interior.ColorIndex
    
15.         End If
    
16.     Next

½Æ»s¥N½X