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

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

¦^´_ 10# wayne0303


¥i¯à¬O³sÄòIJµo,¥i¥H¥Î¥H¤U¤è¦¡¸Õ¸Õ¬Ý
Application.EnableEvents = False
'·|²£¥Í³sÄòIJµoªº¦ì¸m,¨Ò¦p cell.Offset(0, -3).Value = st
Application.EnableEvents = True
IJµo¦¸¼Æ:


¥H¤U¬O´ú¸ÕIJµo¦¸¼Æªº¥N½X
Option Explicit
Dim N&
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xR As Range, st$, cell As Range
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).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
   N = N + 1
If .Column <> 5 Then Exit Sub
   For Each cell In Union(Range([e2], [e65536].End(3)), .Cells)
      st = IIf(Trim(cell) = "", "", IIf(Val(cell.Offset(0, -1)) > 1, "LOB", _
                    IIf(Application.WorksheetFunction.CountIf(cell, "S1A*") > 0, "TM", "MU")))
      cell.Offset(0, -3).Value = st
      N = N + 1
      cell.Offset(0, 3).Resize(, 2) = IIf(cell <> "", 2, "")
      N = N + 1
   Next
End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MUCount&, TMCount&, cellValue, Arr, i&
Arr = Range([B2], [B65536].End(3)).Value
If IsArray(Arr) Then
   MUCount = 0: TMCount = 0
   For i = 1 To UBound(Arr, 1)
      cellValue = Arr(i, 1)
      If Not Cells(i + 1, "P").Font.Strikethrough Then
         If cellValue = "MU" Then
            MUCount = MUCount + 1
            ElseIf cellValue = "TM" Then
               TMCount = TMCount + 1
         End If
      End If
   Next
[A1] = "®×¥ó " & MUCount & "¡þ" & TMCount
N = N + 1
MsgBox "¦X­pIJµo: " & N & " ¦¸"
End If
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD