kimbal

¦^´_ 4# mmggmm

um....  §A¦bexcel¤W°ÝÃD¬O¦b°ÝFÄæ, §Ú´Nª½±µ¬ÝFÄ檺:
F5ªº±ø¥ó:
=COUNTIF(R$6:U$42,$E5)<1
R6:U42 ¤W³£¨S¦³301, ©Ò¥H´N¥X¬õ¦â.

¯d·N¤@ÂI¬O¦pªG¼Æ¾Ú¥[´î¤F, ¬O­n¥Î´¡¤J/§R°£,¦Ó¤£¬O¶K¤W­È
´¡¤J/§R°£ªº¸Ü,±ø¥óªº®æ¦¡·|¸ò·s¥[¦æ¼ÆÁY¤p¼W¤j, ¦ý¶K¤W­Èªº¸Ü´N¤£·|.

mmggmm

kimbal§A¦n,§Ú¥D­n·Q¥ÎQÄ檺¼Æ­È¨Ó§PÂ_A,C,E,G,I,K,M,7Ä椺©Ò¿é¤Jªº¼Æ­È¦³ÉN¿ù»~¦p¦³´N¥ÎÃC¦â«ü¥X¿ù³B

GBKEE

¦^´_ 1# mmggmm
¦h½d³òªºÀx¦s®æ®æ¦¡¤Æ±ø¥ó ªº¤½¦¡ ¶·¥H§@¥Î¤¤ªºÀx¦s®æ¬°¥D
³o¬O§AÀÉ®×A5ªº¤½¦¡¥¿½T  =COUNTIF($Q$6:$Q$65536,$A5)<1
                F5ªº¤½¦¡¿ù»~  =COUNTIF(R$6:U$42,$E5)<1

1. Sub EX()
   
2.     [A5:N65536].FormatConditions.Delete
   
3.     With [A5:A65536,C5:C65536,E5:E65536,G5:G65536,I5:I65536,K5:K65536,M5:M65536]   '¦h½d³òªºÀx¦s®æ
   
4.         .Select              '®æ¦¡¤Æ±ø¥ó¶·°é¿ï½d³ò
   
5.         .Cells(1).Activate   '°é¿ï½d³ò¤¤¤§ A5¬°§@¥Î¤¤ªºÀx¦s®æ
   
6.         .FormatConditions.Delete  '§R°£­ì½d³ò¤¤®æ¦¡¤Æ±ø¥ó
   
7.         .FormatConditions.Add Type:=xlExpression, Formula1:= _
   
8.         "=COUNTIF($Q$6:$Q$77," & .Cells(1).Address(0, 0) & ")<1"
   
9.         '¥H¤Wªº¤½¦¡ =COUNTIF($Q$6:$Q$65536,A5)<1
   
10.         '¦p.Cells(2).Activate  '°é¿ï½d³ò¤¤Åܦ¨ A6¬O§@¥Î¤¤ªºÀx¦s®æ
    
11.         '¥H¤Wªº¤½¦¡ =COUNTIF($Q$6:$Q$65536,A6)<1
    
12.         '§@¥Î¤¤ªºÀx¦s®æ¬°¥D §_«h¤½¦¡´N¿ù±¼
    
13.         .FormatConditions(1).Font.ColorIndex = 3
    
14.         .Cells(1).Select
    
15.     End With
    
16. End Sub

½Æ»s¥N½X

kimbal

©ÎªÌ¥i¥H»¡»¡§A·Q¥Î®æ¦¡¤Æ±ø¥ó°µ¨Ç¤°»ò®ÄªG?