[¤w¸Ñ¨M]±Æ§Ç«á¦b¤£¦P°Ï¶ô¹Ï¤WÃC¦â

jiuhtsair

¦U¦ì¤j¤j¦n¡G Book1_1121.zip (7.72 KB)

¤U¸ü¦¸¼Æ: 14

2011-11-21 20:56

     ¤p§Ì¦³¤@§xÂZ¡A§Ú·Q±Nc4:d22ªº¼Æ¾Ú¥HDÄ欰¥D¥Ñ¤j¦Ó¤p±Æ§Ç..¦A¥ÎD4¨ìD22ªº¼Æ¦r»PD2¤ñ¸û¡A­Y¬O¤ñD2¤j¶W¹L150ªºÀx¦s®æ¥Î¤@ºØÃC¦â¡A¤ñD4¤j¶W¹L80~100¶¡ªºÀx¦s®æ¥Î¥t¤@ºØÃC¦â¡A¤ñD4¤j¶W¹L30~60¶¡ªºÀx¦s®æ¥Î¥t¤@ºØÃC¦â¡A½Ð°Ý¸Ó¦p¦ó³B²z?

     «e­±±Æ§Çªº³¡¥÷..¤p§Ì§Q¥Î¿ý»s¥¨¶°ªº¤è¦¡¤w¸g§¹¦¨¡C«á­±¤ñ¸û¤j¤p¹ÏÃC¦âªº³¡¥÷..¤p§Ì¸ÕµÛ¼g¤Fµ{¦¡¡A¦ý¿ù»~«Ü¦h..¤£ª¾±q¦ó§ï°_¡AÀµ½Ð½ç±Ð¡I

     ¤p§Ìªº·Qªk¬O³o¼Ë...

     Dim i as integer
     For i =4 to 22
     If sheet(1).cells(4,2).Value<=sheet(1).cells(4,i).Value-150 Then cell(4,i).select ..¶îÃC¦â
     elseif.....

     next i
   
     ¥i¬OVBA§i¶D§Ú»¡µ{¦¡¿ù»~..±qIf sheet(1).cells(4,2).Value<=sheet(1).cells(4,i).Value-150 Then cell(4,i).select¤@¸ô³£¬O¬õ¦â¿ù»~..

     ·Q½Ð±Ð¤j®a­n¦p¦ó¸Ñ¤~¦n?ÁÂÁ¡I

jiuhtsair

¦^´_ 7# oobird

·PÁÂoobird¤j¤j¡A³oµ{¦¡½X¯uªº¤Ó´Î¤F¡Iºë²¤S¦³®Ä²v¡C­È±o¤p§Ì³oªì¾ÇªÌ¦h¥[µZ¿i¡B¾Ç²ß¡I·PÁ¡I
ÁÂÁ¡I

jiuhtsair

¦^´_ 6# Hsieh

·PÁÂHsieh¤j¤j¡A³o¥¿¬O¤p§Ì·Q­nªº¤è¦¡¡A¥Ñ©ó¥u¬Oªì¾ÇªÌ¡A·Q¯}ÀY¤]·Q¤£¥X¨Ó¡C
·PÁ±zªº¨ó§U¡IÁÂÁ¡C

oobird

1. Sub Macro4()
   
2.     Range("C4:D22").Sort Key1:=Range("D4"), Order1:=xlDescending
   
3. For Each c In Range("d4:D22")
   
4. c.Interior.ColorIndex = Application.VLookup(c - [d2], [{-10000,0;30,5;61,0;80,6;101,0;150,7}], 2)
   
5. Next
   
6. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 4# jiuhtsair

1. Sub Macro4()
   
2. '
   
3. ' Macro4 Macro
   
4. ' ±M¥ÎªO  ¼Æ¾Ú±Æ§Ç
   
5. ''
   
6.     Range("C4:D22").Sort Key1:=Range("D22"), Order1:=xlDescending, Header:=xlNo, _
   
7.         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
   
8.         :=xlStroke, DataOption1:=xlSortNormal
   
9. 
   
10. For Each a In [C4:C22]
    
11.    k = a.Offset(, 1) - [D2]
    
12.    Select Case k
    
13.    Case Is >= 150
    
14.    n = 3
    
15.    Case 80 To 100
    
16.    n = 6
    
17.    Case 30 To 60
    
18.    n = 32
    
19.    Case Else
    
20.    n = 0
    
21.    End Select
    
22.    a.Resize(, 2).Interior.ColorIndex = n
    
23. Next
    
24. End Sub

½Æ»s¥N½X

jiuhtsair

¦^´_ 3# oobird

oobird¤j¤j¡G
   
        ©êºp¡I¦³¨ÇÃ㤣¹F·N¡I
   
        ¤p§Ì¬O·Q¥ÎD4¨ìD22ªº¤º®e»PD2¬Û¤ñ¸û¡AµM«á¤À§O¦b¤£¦Pªº°Ï¶¡¤¤¥Î¤£¦PÃC¦âªí¥Ü,°Ï¶¡»P°Ï¶¡¤£³sÄò¡A¤£¦b°Ï¶¡¤ºªºÀx¦s®æ«h«O«ù­ì¥»ÃC¦â(¥Õ¦â)¡C
   
        ·PÁ±z¡IÁÂÁ¡I

jiuhtsair

¦^´_ 2# Hsieh

£~£~...·PÁ¤j¤j¡I¯u¬O¦n¤èªk¡C
¥t¥~¡A·Q½Ð±Ð¤j¤j...¨Ï¥Î®æ¦¡¤Æ±ø¥ó¥i¥H¦³¤T²Õ±ø¥ó..¦pªG¤µ¤Ñ»Ý­n¤T²Õ¥H¤W±ø¥ó®É..­n¦p¦ó³B²z©O?

·PÁ±zªº¸Ñ´b¡I·P®¦¡I

oobird

¤ñD2¤j¶W¹L150...
¤ñD4¤j¶W¹L80~100¶¡ªºÀx¦s®æ¡K
¤ñD4¤j¶W¹L30~60¶¡ªºÀx¦s®æ¡K
¬Ý±o«Ü½k¶î¡C¸òD2¡AD4¤ñ¶Ü¡H
¤¤¶¡ªº©O¡H100~150¶¡ªº¡A60~80¶¡ªº¡AÁÙ¦³¤p©ó30ªº

Hsieh

¦^´_ 1# jiuhtsair

¤U¸ü (34.14 KB)

2011-11-21 22:09