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

[µo°Ý] ¥N½X­×¥¿

[µo°Ý] ¥N½X­×¥¿

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xA As Range, xR As Range
Application.ScreenUpdating = False
Set xA = Intersect(Target, Range("U:U"))
If xA Is Nothing Then GoTo xA0
For Each xR In xA
    Range(Cells(xR.Row, 1), Cells(xR.Row, 22)).Font.ColorIndex = 15 ^ -(xR > 1)
Next

xA0:
Set xA = Intersect(Target, Range("O:O"))
If xA Is Nothing Then GoTo xA1
For Each xR In xA
    Range(Cells(xR.Row, 16), xR).Font.ColorIndex = 2 ^ -(xR = "")
Next

xA1:
Set xA = Intersect(Target, Range("J:J"))
If xA Is Nothing Then GoTo xA2
For Each xR In xA
    Range(Cells(xR.Row, 10), xR).Font.Color = RGB(0, 176, 240) ^ -(xR = "L")
    Range(Cells(xR.Row, 10), xR).Font.Bold = (xR = "L")
Next

xA2:
Set xA = Intersect(Target, Range("v:v"))
If xA Is Nothing Then GoTo xA3
For Each xR In xA
    Range(Cells(xR.Row, 1), xR).Font.Color = RGB(153, 102, 0) ^ -((xR Like "*©î¹Ï"))
Next


xA3:
Set xA = Intersect(Target, Range("p:p"))
If xA Is Nothing Then GoTo xA4
For Each xR In xA
    Range(Cells(xR.Row, 1), Cells(xR.Row, 22)).Font.ColorIndex = 5 ^ -(xR.Font.Strikethrough = True)
Next


xA4:
Set xA = Intersect(Target, Range("E:E"))
If xA Is Nothing Then Exit Sub

Dim st As String
Dim cell As Range

For Each cell In Me.Range("E2:E" & Me.Cells(Me.Rows.Count, "E").End(3).Row)
    st = IIf(cell.Value = "", "", _
                IIf(cell.Offset(0, -1).Value > 0, "LOB", _
                    IIf(Application.WorksheetFunction.CountIf(cell, "S1A*") > 0, "TM", "MU")))
    cell.Offset(0, -3).Value = st
   
Next


End Sub

½Ð¤j¤j­ÌÀ°§Ú­×¥¿¤@¤UÂŦⳡ¤Àªº¥N½X¬Ý«ç»ò¼g¤ñ¸û¶¶¡AÁÂÁÂ

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-1-15 10:11 ½s¿è

¦^´_ 1# wayne0303


    ÁÂÁ«e½úµoªí¦¹¥DÃD
µ{§Ç°õ¦æ¥i¥H¥Î,¥i¥H¥¿½T°õ¦æ³Ì­«­n,¦pªG¯à©ú½T«ü¥X°ÝÃDÄpµ²©Î¤W¶Ç½d¨Ò,¤ñ¸û®e©ö²z¸Ñ»Ý¨D
«e½ú´£¨Ñ½d¨Ò²z¸Ñ¦p¤U:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'¡ôÀx¦s®æ¤º®e§ïÅܪºÄ²µo¨Æ¥ó
Dim xA As Range, xR As Range
'¡ô«Å§iÅܼÆ
Application.ScreenUpdating = False
'¡ô¿Ã¹õ¼È¤£ÀHµ{§Ç§ïÅܵ²ªG
Set xA = Intersect(Target, Range("U:U"))
'¡ô¥OxAÅܼƬOIJµo®æ»PUÄ檺¥æ¶°Àx¦s®æ
If xA Is Nothing Then GoTo xA0
'¡ô¦pªGxAÅܼƨS¥æ¶°¨ì¥ô¦óÀx¦s®æ!´N¸õ¨ì¼Ð¥ÜxA0¦ì¸mÄ~Äò°õ¦æ
For Each xR In xA
'¡ô³]³v¶µ°j°é!¥OxRÅܼƬOxA³o¥æ¶°®æªº¤@®æ
    Range(Cells(xR.Row, 1), Cells(xR.Row, 22)).Font.ColorIndex = 15 ^ -(xR > 1)
    '¡ô¥OÀx¦s®æ¤å¦rÃC¦â¬O¦Ç¦â©Î¶Â¦â
    'Àx¦s®æ:xR·í¦CAÄæ¨ìVÄ椧¶¡ªº³sÄòÀx¦s®æ

Next

xA0:
Set xA = Intersect(Target, Range("O:O"))
'¡ô¥OxAÅܼƬOIJµo®æ»POÄ檺¥æ¶°Àx¦s®æ
If xA Is Nothing Then GoTo xA1
'¡ô¦pªGxAÅܼƨS¥æ¶°¨ì¥ô¦óÀx¦s®æ!´N¸õ¨ì¼Ð¥ÜxA1¦ì¸mÄ~Äò°õ¦æ
For Each xR In xA
'¡ô³]³v¶µ°j°é!¥OxRÅܼƬOxA³o¥æ¶°®æªº¤@®æ
    Range(Cells(xR.Row, 16), xR).Font.ColorIndex = 2 ^ -(xR = "")
    '¡ô¥OÀx¦s®æ¤å¦rÃC¦â¬O¥Õ¦â©Î¶Â¦â
    'Àx¦s®æ:xR·í¦COÄæ¨ìPÄ椧¶¡ªº³sÄòÀx¦s®æ

Next

xA1:
Set xA = Intersect(Target, Range("J:J"))
'¡ô¥OxAÅܼƬOIJµo®æ»PJÄ檺¥æ¶°Àx¦s®æ
If xA Is Nothing Then GoTo xA2
'¡ô¦pªGxAÅܼƨS¥æ¶°¨ì¥ô¦óÀx¦s®æ!´N¸õ¨ì¼Ð¥ÜxA2¦ì¸mÄ~Äò°õ¦æ
For Each xR In xA
'¡ô³]³v¶µ°j°é!¥OxRÅܼƬOxA³o¥æ¶°®æªº¤@®æ
    Range(Cells(xR.Row, 10), xR).Font.Color = RGB(0, 176, 240) ^ -(xR = "L")
    Range(Cells(xR.Row, 10), xR).Font.Bold = (xR = "L")
    '¡ô¥OÀx¦s®æ¤å¦rÃC¦â¬O²LÂŦâ©Î¶Â¦â,¥OÀx¦s®æ¤å¦r¬O²ÊÅé©Î«D²ÊÅé
    'Àx¦s®æ:xR·í¦CJÄæÀx¦s®æ

Next

xA2:
Set xA = Intersect(Target, Range("v:v"))
'¡ô¥OxAÅܼƬOIJµo®æ»PVÄ檺¥æ¶°Àx¦s®æ
If xA Is Nothing Then GoTo xA3
'¡ô¦pªGxAÅܼƨS¥æ¶°¨ì¥ô¦óÀx¦s®æ!´N¸õ¨ì¼Ð¥ÜxA3¦ì¸mÄ~Äò°õ¦æ
For Each xR In xA
'¡ô³]³v¶µ°j°é!¥OxRÅܼƬOxA³o¥æ¶°®æªº¤@®æ
    Range(Cells(xR.Row, 1), xR).Font.Color = RGB(153, 102, 0) ^ -((xR Like "*©î¹Ï"))
    '¡ô¥OÀx¦s®æ¤å¦rÃC¦â¬O¤g¦â©Î¶Â¦â
    'Àx¦s®æ:xR·í¦CAÄæ¨ìVÄ椧¶¡ªº³sÄòÀx¦s®æ

Next

xA3:
Set xA = Intersect(Target, Range("p:p"))
'¡ô¥OxAÅܼƬOIJµo®æ»PPÄ檺¥æ¶°Àx¦s®æ
If xA Is Nothing Then GoTo xA4
'¡ô¦pªGxAÅܼƨS¥æ¶°¨ì¥ô¦óÀx¦s®æ!´N¸õ¨ì¼Ð¥ÜxA4¦ì¸mÄ~Äò°õ¦æ
For Each xR In xA
'¡ô³]³v¶µ°j°é!¥OxRÅܼƬOxA³o¥æ¶°®æªº¤@®æ
    Range(Cells(xR.Row, 1), Cells(xR.Row, 22)).Font.ColorIndex = 5 ^ -(xR.Font.Strikethrough = True)
    '¡ô¥OÀx¦s®æ¤å¦rÃC¦â¬OÂŦâ©Î¶Â¦â
    'Àx¦s®æ:xR·í¦CAÄæ¨ìVÄ椧¶¡ªº³sÄòÀx¦s®æ

Next

xA4:
Set xA = Intersect(Target, Range("E:E"))
'¡ô¥OxAÅܼƬOIJµo®æ»PEÄ檺¥æ¶°Àx¦s®æ
If xA Is Nothing Then Exit Sub
'¡ô¦pªGxAÅܼƨS¥æ¶°¨ì¥ô¦óÀx¦s®æ!´Nµ²§ôµ{¦¡°õ¦æ
Dim st As String
Dim cell As Range
'¡ô«Å§iÅܼÆ

For Each cell In Me.Range("E2:E" & Me.Cells(Me.Rows.Count, "E").End(3).Row)
'¡ô³]³v¶µ°j°é!¥OcellÅܼƬO¥»ªíEÄæ¸ÌªºÀx¦s®æ
    st = IIf(cell.Value = "", "", _
                IIf(cell.Offset(0, -1).Value > 0, "LOB", _
                    IIf(Application.WorksheetFunction.CountIf(cell, "S1A*") > 0, "TM", "MU")))
    '¡ô¥OstÅܼƬOIIf()¦^¶Ç­È
    cell.Offset(0, -3).Value = st
    '¡ô¥OcellÅܼƥª3®æÀx¦s®æ­È¬OstÅܼÆ
Next
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾Ç½m²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xR As Range, st$, cell As Range
Application.ScreenUpdating = False
With Target
   If .Column = 21 Then
      For Each xR In .Cells
         Cells(xR.Row, 1).Resize(, 22).Font.ColorIndex = 15 ^ -(Val(xR) > 1)
      Next
   End If
   If .Column = 15 Then
      For Each xR In .Cells
         Cells(xR.Row, 16).Resize(, 2).Font.ColorIndex = 2 ^ -(Trim(xR) = "")
      Next
   End If
   If .Column = 10 Then
      For Each xR In .Cells
         Cells(xR.Row, 10).Font.Color = RGB(0, 176, 240) ^ -(Trim(xR) = "L")
         Cells(xR.Row, 10).Font.Bold = (Trim(xR) = "L")
      Next
   End If
   If .Column = 22 Then
      For Each xR In .Cells
         Cells(xR.Row, 1).Resize(, 22).Font.Color = RGB(153, 102, 0) ^ -((xR Like "*©î¹Ï"))
      Next
   End If
   If .Column = 16 Then
      For Each xR In .Cells
         Cells(xR.Row, 1).Resize(, 22).Font.ColorIndex = 5 ^ -(xR.Font.Strikethrough = True)
      Next
   End If
   If .Column <> 5 Then Exit Sub
   For Each cell In Me.Range("E2:E" & Me.Cells(Me.Rows.Count, "E").End(3).Row)
      st = IIf(Trim(cell) = "", "", IIf(Val(cell.Offset(0, -1)) > 0, "LOB", _
                    IIf(Application.WorksheetFunction.CountIf(cell, "S1A*") > 0, "TM", "MU")))
      Application.EnableEvents = False
      cell.Offset(0, -3).Value = st
      Application.EnableEvents = True
   Next
End With
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 2# Andy2483

·PÁÂandy¤j¤j¦^ÂÐ~

¤U­±³o¬q¥N½X¦b°õ¦æªº®É­Ô·|¦³©µ¿ðªºª¬ªp

  • ¹³§Ú³£¨ú®øEÄ檺­È¤F¡ABÄ檺µ²ªGÁÙ¬O¨S¦³¸òµÛ¨ú®ø....
  • E3¡BD3¦³­È¡AB3Åã¥Üµ²ªG¡A¦bE4¿é¤J¼Æ¾Ú¡AB3¤~·|µ¹§Ú¸õ¥¿½Tªºµ²ªG³o¼Ë...


    xA4:
Set xA = Intersect(Target, Range("E:E"))
If xA Is Nothing Then Exit Sub

Dim st As String
Dim cell As Range

For Each cell In Me.Range("E2:E" & Me.Cells(Me.Rows.Count, "E").End(3).Row)
    st = IIf(cell.Value = "", "", _
                IIf(cell.Offset(0, -1).Value > 0, "LOB", _
                    IIf(Application.WorksheetFunction.CountIf(cell, "S1A*") > 0, "TM", "MU")))
    cell.Offset(0, -3).Value = st
   
Next

TOP

¦^´_ 4# wayne0303


    ¥i¯à¬O³sÄòIJµo,½Ð¸Õ¸Õ3#ªº¤è®×
      Application.EnableEvents = False
      cell.Offset(0, -3).Value = st
      Application.EnableEvents = True
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ wayne0303 ©ó 2024-1-15 10:58 ½s¿è

¦^´_ 5# Andy2483


¤]¬O¤@¼Ë...

ÁÙ¬O¬O­n§ï¥Î±z3# ªºwith¼g©O¡H

2024-01-15_103035_New.jpg (20.29 KB)

2024-01-15_103035_New.jpg

TOP

¦^´_ 6# wayne0303


For Each cell In Union(Me.Range("E2:E" & Me.Cells(Me.Rows.Count, "E").End(3).Row), .Cells)
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ wayne0303 ©ó 2024-1-16 09:09 ½s¿è

¦^´_ 7# Andy2483



·PÁÂAndy¤j~
·Q¦A½Ð°Ý¤@¤U¡A§Ú¥[¤FÂŦⳡ¤Àªº¥N½X¡A¦ý±NeÄæ²MªÅªº®É­Ô¡AhÄ檺¼Æ¦r«o¶]¨ì¤Fh1?(¥¿±`¬O­n¸òµÛ¬°ªÅ¡A¦ý«o§ï¤F§Úh1¥»¨Óªº¦r)
ÁÂÁ¡I

If .Column <> 5 Then Exit Sub
   For Each cell In Union(Range("E2:E" & Cells(Rows.Count, "E").End(3).Row), .Cells)
      st = IIf(Trim(cell) = "", "", IIf(Val(cell.Offset(0, -1)) > 0, "LOB", _
                    IIf(Application.WorksheetFunction.CountIf(cell, "S1A*") > 0, "TM", "MU")))
      cell.Offset(0, -3).Value = st
      cell.Offset(0, 3).Resize(, 2) = IIf(cell <> "", 2, "")
   Next

2024-01-16_085826.jpg (28.81 KB)

2024-01-16_085826.jpg

TOP

¦^´_ 8# wayne0303


    §Ú´ú¸Õ¨S°ÝÃD,¦AÀˬd¤@¤U©Î§ó©ú½T»¡©ú/¹Ï¸Ñ/¤W¶Ç½d¨Ò
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 9# Andy2483


¦pªþ¥ó¡A¦A³Â·ÐAndy¤j¬d¬Ý¤@¤U
ÁÙ¦³A1¦X¨ÖÀx¦s®æªº®×¥ó²Î­p¤]·|¦³©µ¿ðªºª¬ªp

ÁÂÁÂ

²Î­p.rar (26.48 KB)

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD