samwang

¦^´_  cypd

¤£¦n·N«ä¦U¦ì¥ý¶i...
°w¹ïªì©lÀɮײ{¦³¤@°ÝÃD¥X²{



Àɮ׳¡¤À C Äæ¤á¦W­ì³]¦³®æ¦¡¤Æ±ø ...
cypd µoªí©ó 2022-7-22 17:00

³]©w®æ¦¡¤Æ-->­«½Æ­È¡A³o¼ËÀ³¸Ó¥i¥H§a?

samwang

¦^´_  samwang

·PÁ±zªº¦^ÂÐ
©Ò´£¨Ñ¤§¤è¦¡¦³¸Õ¹L...µ§¼Æ·U¦h®É·|³y¦¨©Ò­z¶°é°é°ÝÃD
cypd µoªí©ó 2022-7-23 09:18

10¼Ó¤è¦¡¬O¥Î¤º«Ø¡A§Aªº11¼Ó¤è¦¡¬O¥Î¤½¦¡¡A©Ò¥H¦³¥i¯à·|¤ñ¸ûºC
½Ð¦b½T»{¬Ý¬Ý¡AÁÂÁÂ

samwang

¦^´_ 11# cypd

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr, xD, C%, T$, m&, i&
If Target.Column = 3 Then C = 3
If Target.Column = 4 Then C = 4
Set xD = CreateObject("Scripting.Dictionary")
With Sheets("«È¤á°ò¥»¸ê®Æ")
    Arr = .Range(.Cells(1, C), .Cells(Rows.Count, C).End(3))
    .Range(.Cells(1, C), .Cells(Rows.Count, C).End(3)).Font.ColorIndex = 0
    .Range(.Cells(1, C), .Cells(Rows.Count, C).End(3)).Interior.ColorIndex = 0
    For i = 1 To UBound(Arr)
        T = Arr(i, 1)
        If xD.Exists(T) Then
            m = xD(T)
            Cells(m, C).Font.ColorIndex = 3
            Cells(m, C).Interior.ColorIndex = 36
            Cells(i, C).Font.ColorIndex = 3
            Cells(i, C).Interior.ColorIndex = 36
        End If
        xD(T) = i
    Next
End With
End Sub

samwang

¦^´_  samwang
¤Ó·PÁ±z¤F...samwang

³t«×¤è­±°ÝÃD¤w OK
¸g´ú¸Õ¥t¦³°ÝÃD¥X²{¦p¤U
­Y¬O C ©Î D Äæ¥X²{ ...
cypd µoªí©ó 2022-7-24 11:33

­×§ï¦p¤U²¾°£.End(3)¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
With Sheets("«È¤á°ò¥»¸ê®Æ")
    Arr = .Range(.Cells(1, C), .Cells(Rows.Count, C).End(3))
    .Range(.Cells(1, C), .Cells(Rows.Count, C)).Font.ColorIndex = 0
    .Range(.Cells(1, C), .Cells(Rows.Count, C)).Interior.ColorIndex = 0

samwang

¦^´_  samwang

¤Ó·PÁ±z¤F...samwang

C ©Î D Äæ¥X²{¬Û¦P¼Æ¾Ú®É...§R°£¤U¦C¼Æ¾Ú¤§«áÀx¦s®æ·|¦³¶ñº¡¤§ ...
cypd µoªí©ó 2022-7-24 20:23

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr, xD, C%, T$, m&, i&
If Target.Column = 3 Then C = 3
If Target.Column = 4 Then C = 4
If C = 0 Then Exit Sub
Set xD = CreateObject("Scripting.Dictionary")
With Sheets("«È¤á°ò¥»¸ê®Æ")
    .Unprotect Password:="1234"  '¸Ñ«OÅ@¡A±K½X¦Û¦æ­×§ï
     Arr = .Range(.Cells(1, C), .Cells(Rows.Count, C).End(3))
     .Range(.Cells(1, C), .Cells(Rows.Count, C)).Font.ColorIndex = 0
     .Range(.Cells(1, C), .Cells(Rows.Count, C)).Interior.ColorIndex = 0
     For i = 1 To UBound(Arr)
         T = Arr(i, 1)
         If xD.Exists(T) Then
             m = xD(T)
             .Cells(m, C).Font.ColorIndex = 3
             .Cells(m, C).Interior.ColorIndex = 36
             .Cells(i, C).Font.ColorIndex = 3
             .Cells(i, C).Interior.ColorIndex = 36
         End If
         xD(T) = i
     Next
    .Unprotect Password:="1234"  '«OÅ@¡A±K½X¦Û¦æ­×§ï
End With
End Sub

samwang

¦^´_ 18# cypd

¥t¥~
.Unprotect Password:="1234"  '«OÅ@¡A±K½X¦Û¦æ­×§ï(¦h¤F Un µLªk¶i¦æ«OÅ@)
¤w§R°£ Un¡K´ú¸Õ OK

>> ¤£¦n·N«ä¡A¨S¦³§ó·s¨ì¡A¦p±z­×§ïUN­n§R°£¡AÁÂÁÂ

samwang

¦^´_ 20# cypd

VBA¤½¦¡§@°Ê«á
­Y C Ä榳·s¼Æ¾Ú¿é¤J®É·|³y¦¨ C1 ©³¦â¤£¨£
­Y D Ä榳·s¼Æ¾Ú¿é¤J®É·|³y¦¨ D1 ©³¦â¤£¨£
>> §ï¥Ñ²Ä2¦C¶}©l¦p¤U
With Sheets("«È¤á°ò¥»¸ê®Æ")
    .Unprotect Password:="1234"  '¸Ñ«OÅ@¡A±K½X¦Û¦æ­×§ï
     Arr = .Range(.Cells(1, C), .Cells(Rows.Count, C).End(3))
     .Range(.Cells(2, C), .Cells(Rows.Count, C)).Font.ColorIndex = 0
     .Range(.Cells(2, C), .Cells(Rows.Count, C)).Interior.ColorIndex = 0

­ì«OÅ@¤u§@ªí¤º¤§³]©w¥¢®Ä
(¦Û¦æ³]©w¦C®æ¦¡¤Î¨Ï¥Î¦Û°Ê¿z¿ïµLªk¨Ï¥Î...«ì´_¹w³]­È)
>> ¨Ì±z­ì¨Óªº®æ¦¡­×§ï¦p¤U¡A½Ð¦A¸Õ¬Ý¬Ý¡AÁÂÁÂ
.Protect Password:="1234", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingRows:=True, AllowFiltering:=True '«OÅ@¡A±K½X¦Û¦æ­×§ï