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

wayne0303

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ÁÂÁÂ

wayne0303

¦^´_ 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

wayne0303

¦^´_ 5# Andy2483


¤]¬O¤@¼Ë...

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

wayne0303

¦^´_ 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

wayne0303

¦^´_ 9# Andy2483


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

ÁÂÁÂ