[µo°Ý] ½Ð²¤Æ¿ý»sªºµ{¦¡½X¡C

ziv976688

´ú¸ÕÀÉ : ®æ¦¡¤Æ±ø¥ó¤½¦¡.rar (7.64 KB)

¤U¸ü¦¸¼Æ: 6

2019-5-21 05:39

¥H¤U¬OB2¡JF2,G2¡JK2,......, AU2¡JAX2®æ¦¡¤Æ±ø¥ó¤½¦¡¿ý»sªºµ{¦¡½X¡J
    Range("B2:F2").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(B2=MAX($B2:$F2))*(B2>0)"
    Selection.FormatConditions(1).Interior.ColorIndex = 43
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((B2<=$B2:$F2)/COUNTIF($B2:$F2,$B2:$F2))=2)*(B2>0)"
    Selection.FormatConditions(2).Interior.ColorIndex = 8
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((B2<=$B2:$F2)/COUNTIF($B2:$F2,$B2:$F2))=3)*(B2>0)"
    Selection.FormatConditions(3).Interior.ColorIndex = 37
   
    Range("G2:K2").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(G2=MAX($G2:$K2))*(G2>0)"
    Selection.FormatConditions(1).Interior.ColorIndex = 43
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((G2<=$G2:$K2)/COUNTIF($G2:$K2,$G2:$K2))=2)*(G2>0)"
    Selection.FormatConditions(2).Interior.ColorIndex = 8
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((G2<=$G2:$K2)/COUNTIF($G2:$K2,$G2:$K2))=3)*(G2>0)"
    Selection.FormatConditions(3).Interior.ColorIndex = 37
¡J
¡J
    Range("AU2:AX2").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(AU2=MAX($AU2:$AX2))*(AU2>0)"
    Selection.FormatConditions(1).Interior.ColorIndex = 43
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((AU2<=$AU2:$AX2)/COUNTIF($AU2:$AX2,$AU2:$AX2))=2)*(AU2>0)"
    Selection.FormatConditions(2).Interior.ColorIndex = 8
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(SUMPRODUCT((AU2<=$AU2:$AX2)/COUNTIF($AU2:$AX2,$AU2:$AX2))=3)*(AU2>0)"
    Selection.FormatConditions(3).Interior.ColorIndex = 37

¦@10¬q(½Ð¸Ô¨£´ú¸ÕÀÉ)

½Ð°Ý¡J¥i¥H¦A²¤Æ¶Ü?

PS¡J
¤½¦¡­n«O¯d¡C
¦pªG¥u¯à¤@¬q¤@¬q§ï¡A´N½Ð¥u§ï¤@¬q´N¥i¥H¤F¡A¨ä¥¦9¬q§Ú¦A®M¼g´N¦n!

ÁÂÁÂÀ°¦£!

ziv976688

¦^´_ 16# GBKEE

§A¤Ó«È®ð¤F^^
¤£½×¹Lµ{¡A¥u½×µ²ªG~¦³¤F³Ì«á¥¿½Tªºµ²ªG¡A§Ú³£¬O¤ß¦s·P¿Eªº¡C

¦A±Nµ´¹ï¦ì§}Rng.Address §ï¦¨µ´¹ïÄæ¦ì§}Rng.Address(0, 1)
§¹¦¨¤F!
·PÁ§Aªº­@¤ß«ü¾É©MÀ°¦£

GBKEE

¦^´_ 15# ziv976688

¹ï¤£°_°Õ,¨S»{¯u¬Ý§Aªº°ÝÃD
À³¥[¤W

1. Set Rng = Rng.Offset(, Rng.Columns.Count) '**¤U¤@­Ó½d³ò
   
2.   If i = 8 Then Set Rng = Rng.Cells(1).Resize(, Rng.Columns.Count - 1)

½Æ»s¥N½X

ziv976688

¦^´_ 14# GBKEE

Set Rng = Rng.Offset(, IIf(i < 9, Rng.Columns.Count, Rng.Columns.Count - 1))  '**¤U¤@­Ó½d³ò
©M
Set Rng = Rng.Offset(, IIf(i < 8, Rng.Columns.Count, Rng.Columns.Count - 1))  '**¤U¤@­Ó½d³ò
¤G¬q¦¨¦¡½X³£¬O­pºâ¨ì¦h1Äæ(AYÄæ)¡F
¥HFor i = 0 To 9 ¨Ó»¡
À³¸Ó¬Oi < 9¤~¬O¥¿½Tªº
¥u¬O¤£ª¾¬O¬Æ»ò­ì¬G¡A.Columns.Count - 1µL®Ä¡C
¬Æ¦Ü§ï¬°.Columns.Count - 2ÁÙ¬O­pºâ¨ìAYÄæ¡C
·Ð½Ð¦A«ü¥¿!ÁÂÁ§A!

GBKEE

¦^´_ 13# ziv976688

½Ð­×¥¿¬°

1. Set Rng = Rng.Offset(, IIf(i < 8, Rng.Columns.Count, Rng.Columns.Count - 1)) '**¤U¤@­Ó½d³ò

½Æ»s¥N½X

ziv976688

¦^´_ 11# GBKEE
®æ¦¡¤Æ±ø¥ó¤½¦¡_G¤j.rar (8.95 KB)

¤U¸ü¦¸¼Æ: 3

2019-5-24 14:54

¦C25          Set Rng = Rng.Offset(, IIf(i < 9, Rng.Columns.Count, Rng.Columns.Count - 1)) '**¤U¤@­Ó½d³ò

¤£¦n·N«ä¡A¤µ¤Ñ­n¨Ï¥Î¡A¤£ª¾¹D¬°¤°»òÁÙ¬O¦h¤F¤@Äæ(AY2)^^"
¥i§_½Ð§A¦A«ü¥¿?ÁÂÁ§A!

ziv976688

¦^´_ 11# GBKEE

§¹¦¨¤F!
·PÁ§Aªº«ü±Ð©MÀ°¦£

GBKEE

¦^´_ 10# ziv976688
¬d¬ÝVba»¡©ú  Address ÄÝ©Ê ,¦Û¦æ¸Õ¸Õ­×§ï


³Ì«á¤@¬q(²Ä10¬q)$AU : $AX2  ->¥u¦³4Äæ
¤Ö¤@Äæ´N´î1

1. Set Rng = Rng.Offset(, IIf(i < 9, Rng.Columns.Count, Rng.Columns.Count - 1))

½Æ»s¥N½X

ziv976688

¦^´_ 4# GBKEE
¦³µ§»~~­«·s¦^ÂЩM»¡©ú¡C

·PÁ¸ѵª¡C
½Ð¦A­×¥¿³Ì«á¤@¬q(²Ä10¬q)$AU2 : $AX2  ->¥u¦³4Äæ¡F¤£¬O$AU2 : AY2
°£¤F For i = 0 To 9 §ï¦¨= 0 To 8
½Ð°Ý : ²Ä10¬q­n«ç»ò¸É¼g?

ÁÙ¦³°õ¦æ«áªº¤½¦¡ªº"¦C¦ì"¦h¤Fµ´¹ï²Å¸¹ " $ " ->EX : $B2 : $F2Åܦ¨ $B$2 : $F$2 ¡F$G2 : $K2Åܦ¨ $G$2 : $K$2¡F.......¡F$AP2 : $AT2Åܦ¨$AP$2 : $AT$2¡F$AU2 : $AX2Åܦ¨$AU$2 : $AX$2
©Ò¥HµLªk¦A¥Î"¤j±½§â"½Æ»s®æ¦¡¨ì¨ä¥¦¦C¡C
½Ð°Ý : ­n¦p¦ó­×§ï?

¥H¤W  ·Ð½Ð§A­×¥¿¡CÁÂÁ§A^^

ziv976688

¦^´_ 8# Scott090
¤£¦n·N«ä¡A§Ú¦bµo°Ý®É¡A´N¦³¯S§Oµù©ú"¤½¦¡­n«O¯d"~¥i¯à§A¨S¦³ª`·N¨ì
ÁÙ¬O«D±`·PÁ§A¤@¦AªºÀ°¦£