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

½Ð°Ýsheet Äæ¦ìÂà´«°ÝÃD

¦^´_ 29# Hsieh


    ½Ð°Ý¤@¤U ¤§«eªºµ{¦¡¤¤
     JÄ椧«e¿é¤J¡yOK¡z·|¤Ï¬õ
¥i¥H§ï¦¨JÄ榳¿é¤J®É¶¡ªº¸Ü¤Ï¬õ
                      ¥u¦³¿é¤JIÄæ¨S¦³¿é¤JJÄæ«h¤Ïºñ
¤u³æ¸¹½X¬Û¦P¤§¤U     J2©MI3ªº®É¶¡®t¶W¹L4¤p®É«h¤Ï¶À
¨ä¾l«hªÅ¥Õ
ÁÂÁÂ
Tony

TOP

¦^´_ 31# tonycho33

¸Õ¸Õ¬Ý
  1. Private Sub Worksheet_Activate()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. Set d1 = CreateObject("Scripting.Dictionary")
  4. With Sheet6
  5. For Each A In .Range("I2", .[I65536].End(xlUp)).SpecialCells(xlCellTypeConstants)
  6.    If A <> "" And A.Offset(, 1) = "" Then
  7.    mystr = Join(Application.Transpose(Application.Transpose(A.Offset(, -8).Resize(, 5).Value)), "")
  8.    d(mystr) = d(mystr) + 1
  9.    ElseIf A.Offset(, 1) - A Then
  10.    mystr = Join(Application.Transpose(Application.Transpose(A.Offset(, -8).Resize(, 5).Value)), "")
  11.    d1(mystr) = d1(mystr) + 1
  12.    End If
  13. Next
  14. End With
  15. With Me
  16. r = 2
  17. Do Until .Cells(r, 1) = ""
  18. Set A = .Cells(r, 1)
  19. mystr = A & A.Offset(, 1)
  20.    For Each c In .Range(A.Offset(, 6), A.Offset(, 6).End(xlToRight))
  21.       temp = mystr & Join(Application.Transpose(c.Resize(3, 1)), "")
  22.       If d.exists(temp) = True Then
  23.       c.Resize(3, 1).Interior.ColorIndex = 4
  24.       ElseIf d1.exists(temp) = True Then
  25.       c.Resize(3, 1).Interior.ColorIndex = 6
  26.       Else
  27.       c.Resize(3, 1).Interior.ColorIndex = 0
  28.       End If
  29.    Next
  30. r = r + 3
  31. Loop
  32. End With
  33. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 32# Hsieh


    ½Ð°Ý¤@¤U
²{¦ba sheetÂàb sheet®æ¦¡®É¡A¤§«e¬O¤@¦¸¾ã­±Âà´«
¥i§_¤@¦¸¤T¦CÂà¡]¨C­Ó¤u³æ¤U¤è³£¦³«ö¶s¡^¡A ¤§«e¤Ï¬õ¤Î¤Ïºñªº¥\¯à¯àÄ~Äò¨Ï¥Î
¨Ò¦p¡G
¥Ø«e1286883¤u³æ¤U¤èªº«ö¶s¡A«ö¤@¤U¦AÂà¨ìb sheet
¥H¦¹Ãþ±À¡A«h·|©¹¤U¬ö¿ý¡A¦pªG¤w¦s¦bªº¤u³æ¡A§ó§ï¹LG¨ìSÄæ®É¡A¤ñ¹ï¤u³æ¸¹½X¡A«ö¤U«ö¶s«á¡A«hb sheet·|¸òµÛÅÜ°Ê
·sªº¤u³æ«h©¹¤UÄ~Äò¬ö¿ý
¨Ã¥B¥i¥H¸õµÛ¿é¤J¤£¤@©w­n«ö·Óa sheetªº¶¶§Ç

ÁÂÁÂ

sheet Äæ¦ìÂà´«.rar (16.39 KB)

Tony

TOP

½Ð¨D¦U¦ì¨ó§U
ÁÂÁÂ
Tony

TOP

        ÀR«ä¦Û¦b : ¦³´¼¼z¤~¯à¤À¿ëµ½´c¨¸¥¿¡F¦³Á¾µê¤~¯à«Ø¥ß¬üº¡¤H¥Í¡C
ªð¦^¦Cªí ¤W¤@¥DÃD