ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¿ý»sªºµ{¦¡½X¡A°õ¦æ²£¥ÍBUG

[µo°Ý] ¿ý»sªºµ{¦¡½X¡A°õ¦æ²£¥ÍBUG

´ú¸ÕÀÉ : ¿ý»sªºµ{¦¡½X¡A°õ¦æ²£¥ÍBUG_1.rar (9.58 KB)
Sheet1ªºµ{¦¡½X¬O¨Ì¾ÚSheet2®æ¦¡¤Æªº3­Ó±ø¥ó¤½¦¡©Ò¿ý»sªº¡A
¤£ª¾¬°¤°»òSheet1ªºµ{¦¡½X°õ¦æ«á·|²£¥ÍBUG¡H
BB:BEÄ檺¼Ð¥Ü¤£¥¿½T(½Ð¸Ô¨£Sheet2! BB:BEªº¥¿½T¼Ð¥Ü)¡C

½Ð°Ý¡JSheet1¿ý»sªºµ{¦¡½X¡A¦³­þ¸Ì¿ù»~¤F¡H
½Ð¦U¦ì¤j¤j«ü¥¿¡IÁÂÁ¡I

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2019-12-6 08:46 ½s¿è

¸É¥R3­Ó±ø¥ó¤½¦¡ªºÅÞ¿è :
¿ï¨úBB17:BX80
±ø¥ó1 :
·íBBÄ檺(ROW(16:79)/7)¾l¼Æ=2ªº¦U¦C¤§³Ì¤p­È¥B¥B<5®É¡A«h±N¨ä¦PÄ檺¤U¤@­ÓÀx¦s®æ¼Ð¥Ü40¸¹©³¦â+¬õ¦â¦rÅé¡C
±ø¥ó2 :
·íBBÄ檺(ROW(16:79)/7)¾l¼Æ=2ªº¦U¦C¤§³Ì¤p­È®É¡A«h±N¨ä¦PÄ檺¤U¤@­ÓÀx¦s®æ¼Ð¥Ü40¸¹©³¦â+ºñ¦â¦rÅé¡C
±ø¥ó3 :
·íBBÄ檺(ROW(16:79)/7)¾l¼Æ=2ªº¦U¦C¤§­È<10®É¡A«h±N¨ä¦PÄ檺¤U¤@­ÓÀx¦s®æ¼Ð¥Üºñ¦â¦rÅé¡C

®æ¦¡©¹¥k½Æ»s¨ìBXÄæ¡C
ÁÂÁÂ !

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2019-12-6 14:10 ½s¿è

»¡©úªºµ§»~­×¥¿ :
¿ï¨úBB17:BX80­×¥¿¬°¿ï¨úBB17:BK80
®æ¦¡©¹¥k½Æ»s¨ìBXÄæ­×¥¿¬°®æ¦¡©¹¥k½Æ»s¨ìBKÄæ
ÁÂÁÂ !
===========================================
Sheet1¿ý»sªºµ{¦¡½X¦p¤U :

Sub Macro1()
'
' Macro1 Macro
' MC SYSTEM ¦b 2019/12/6 ¿ý»sªº¥¨¶°
'

'

    Range("BB17:BK80").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(MIN(IF((MOD(ROW($16:$79),7)=2),BB$16:BB$79))=BB16)*(MOD(ROW(BB16),7)=2)*(BB16<5)"
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions(1).Interior.ColorIndex = 40
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(MIN(IF((MOD(ROW($16:$79),7)=2),BB$16:BB$79))=BB16)*(MOD(ROW(BB16),7)=2)"
    Selection.FormatConditions(2).Font.ColorIndex = 10
    Selection.FormatConditions(2).Interior.ColorIndex = 40
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(MOD(ROW(BB16),7)=2)*(BB16<10)"
    Selection.FormatConditions(3).Font.ColorIndex = 10
   
End Sub

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2019-12-7 06:02 ½s¿è

´ú¸ÕÀɪº¤º®e©M¾Þ§@»¡©ú :
1_Sheet1&Sheet2ªºBB17:BK80¤§3­Ó®æ¦¡¤Æ±ø¥ó¤½¦¡³£¬Û¦P¡C
2_¦bSheet1³]¥ß1­Ó°õ¦æ«ö¶sÁä¡C
3_±NSheet1ªº3­Ó®æ¦¡¤Æ±ø¥ó¤½¦¡¿ý»s¥¨¶°¡C
4_Sheet2®æ¦¡¤Æ±ø¥ó¤½¦¡ªºÃC¦â¼Ð¥Ü¡A¯d§@¤ñ¹ï¥Î¡C

°ÝÃD :
«öSheet1ªº°õ¦æ«ö¶sÁä«á¡A¨äBB :BEÄ檺¼Ð¥Ü¤£¥¿½T(§Y»PSheet2ªº¯d©³ÃC¦â¼Ð¥Ü¤£¦P)¡C

½Ð°Ý :
Sheet1¿ý»sªº¥¨¶°µ{¦¡½X¡A­þ¸Ì¦³¿ù»~ ? ­n¦p¦ó­×¥¿ ?
ÁÂÁÂ !

PS :
¦]¬°Sheet1¦pªG¬O¿ï¨úBB17:BK80«á¡AÂI¤W¤è¤u¨ã¦Cªº"®æ¦¡"¡A¦AÂI"³]©w®æ¦¡¤Æªº±ø¥ó"¡A¦AÂI½T©w«á¡A«hSheet1ªºÃC¦â¼Ð¥Ü´N§¹¥þ¥¿½T(§Y»PSheet2ªº¯d©³ÃC¦â¼Ð¥Ü§¹¥þ¬Û¦P)¡A
©Ò¥H»{¬°3­Ó®æ¦¡¤Æªº±ø¥ó¤½¦¡¡AÀ³¸Ó¬O¨S¦³°ÝÃD¡C

TOP

With Range("BB17:BK80")
    .Select
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MMULT(1,MIN(OFFSET(BB$16,ROW($1:$10)*7-7,))/($BA17=""³Ì¤p"")/(BB16<5))=BB16"
    .FormatConditions(1).Font.ColorIndex = 3
    .FormatConditions(1).Interior.ColorIndex = 40
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MMULT(1,MIN(OFFSET(BB$16,ROW($1:$10)*7-7,))/($BA17=""³Ì¤p""))=BB16"
    .FormatConditions(2).Font.ColorIndex = 10
    .FormatConditions(2).Interior.ColorIndex = 40
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=($BA17=""³Ì¤p"")*(BB16<10)"
    .FormatConditions(3).Font.ColorIndex = 10
End With

À³¸Ó¬O°}¦C¤½¦¡ªº°ÝÃD, ¥²¶·¤â°Ê¦A½T©w,
§ï¦¨¤@¯ë¤½¦¡~~

========================

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2019-12-8 16:53 ½s¿è

¦^´_ 5# ­ã´£³¡ªL
­ã´£ª©¥D : ±z¦n!
¦]¬°­ì®æ¦¡¤Æªº±ø¥ó¤½¦¡¥H"¤â°Ê"¾Þ§@®É¡AÃC¦â¼Ð¥Ü³£¨S¦³°ÝÃD¡A¥u¬O¥H"«ö¶s"°õ¦æ¿ý»sªºµ{¦¡½X®É¡AÃC¦â¼Ð¥Ü¤~·|¦³BUG¡A©Ò¥H§Ú¨S¦³¦Ò¼{¨ì¤½¦¡·|¬O°}¦C

¶Q¸ÑÃD¤½¦¡¸g"¤â°Ê"©M"«ö¶s"¾Þ§@´ú¸Õ«á¡AÃC¦â¼Ð¥Ü§¹¥þ¥¿½T¡C
ÁÂÁ±z!·P®¦

TOP

        ÀR«ä¦Û¦b : §Ñ¥\¤£§Ñ¹L¡A§Ñ«è¤£§Ñ®¦¡C
ªð¦^¦Cªí ¤W¤@¥DÃD