[µo°Ý] ¤£ª¾µo¥Í¥X¤F¬Æ»òª¬ªp¡AÁٽЦU¦ì¤j¤jµ¹¤©¨ó§U

indigpfbi

¥H¤Uµ{¦¡¤£ª¾¥X¤F¬Æ»òª¬ªp¡AÁٽЦU¦ì¤j¤j¨ó§U±Æ°£
Private Sub Worksheet_Change(ByVal TARGET As Range)
Dim i As Long
i = 3
Do While Cells(i, "A") <> Sheets("®w¦sÁ`ªí").[B2:B65536]


With TARGET


'If .Row >= 1 And .Column = 1 Then

  .Offset(0, 2) = Application.VLookup(.Value, Sheets("®w¦sÁ`ªí").[B2:K65536], 3, False)
  .Offset(0, 3) = Application.VLookup(.Value, Sheets("®w¦sÁ`ªí").[B2:K65536], 4, False)
  .Offset(0, 4) = Application.VLookup(.Value, Sheets("BOM").[A2:K65536], 5, False)
  .Offset(0, 5) = Application.VLookup(.Value, Sheets("BOM").[A2:K65536], 5, False) * Range("B1")
  .Offset(0, 6) = Application.VLookup(.Value, Sheets("®w¦sÁ`ªí").[B2:K65536], 5, False)
  .Offset(0, 7) = Application.VLookup(.Value, Sheets("®w¦sÁ`ªí").[B2:K65536], 5, False) _
                    - Application.VLookup(.Value, Sheets("BOM").[A2:K65536], 5, False) * Range("B1")
  .Offset(0, 8) = Application.VLookup(.Value, Sheets("®w¦sÁ`ªí").[B2:K65536], 8, False)
  
    If .Offset(0, 7) < Application.VLookup(.Value, Sheets("®w¦sÁ`ªí").[B2:K65536], 8, False) Then
   
       .Offset(0, 7).Font.Color = RGB(255, 0, 0)
      
    Else
      
       .Offset(0, 7).Font.Color = RGB(0, 0, 0)
      
    End If



End With
i = i + 1
Loop


End Sub

°õ¦æ®É¦b²Ä¤T¦æ¥X²{«¬ºA¤£²Å¦Xªº¿ù»~°T®§µøµ¡¡A¦Ê«ä¤£±o¨ä¸Ñ
Á٧Ʊæ¦U¦ì¤j¤j¯à¨ó§U½T»{
ÁÂÁÂ

indigpfbi

¤p§Ì¬O­n»s§@¤@­Ó®w¦sºÞ²z¨t²Î¡A§Æ±æ¯à°÷¦b¤u§@­¶¤WKey¤J²£«~®Æ¸¹«á¡A¤u§@­¶´N·|±q¸ê®Æ®w¤¤§â¦¹²£«~ªº¬ÛÃö¹s¥ó¦C¥X¡A³Ì«á¦A¶ñ¤J»Ý¨D¼Æ¶q¡A¦¹¤u§@­¶´N¯àÀ°¦£±a¥X¦U¹s¥ó¹ê»Ú¤ä¥X¼Æ¶q¡A³Ì²×¦b§â¦¹¹ê»Ú¤ä¥X¼Æ¶q»P¸ê®Æ®w¤º¬ÛÃö¹s¥ó¬Û´î«áªºµ²ªG¼g¤J¸ê®Æ®w

¦]¬°¤p§Ì¹ïVBAÁÙ¤£¬Æ¼ô±x¡AÁٽнu¤W¦U¦ì°ª¤â¤j¤j¨ó§U§¹¦¨
ÁÂÁÂ

prince120101

¦^´_ 2# indigpfbi

indigpfbi¤j
§A­n¤£­n´£¨Ñ¤@­Ó°Ê§@¬yµ{ ¹ÏorÀÉ®×
³o¼Ë·|¤ñ¸û¦n²z¸Ñ~

lpk187

§Úªº°µªk , «Ü¤Ö±a¤J¨ç¼Æ¼g³£¥Î¥H¤Uªº°µªk

²£«~½s¸¹ = [A1]
XX = Worksheets("¤u§@ªí2").Columns(1).Find(²£«~½s¸¹, , , xlWhole, , xlPrevious).Row '§ä´M¸Ó²£«~ªº¦æ¸¹
[B1] = Worksheets("¤u§@ªí2").Cells(2, XX) '¨Ò¦p²£«~¦WºÙ
[C1] = Worksheets("¤u§@ªí2").Cells(3, XX) '¨Ò¦p²£«~ªº³æ»ù
'¨Ì¦¹Ãþ±À

indigpfbi

·PÁ¦U¦ì¤j¤j¨ó§U
¥Ø«e¼È®É¤w§ä¨ì¸Ñ¨M¤èªk
­×¥¿¦¨§Q¥Î¬J¦³³Æ®Æªí¡A¹ï¸ê®Æ®w¶i¦æ¤ñ¹ï¡A¦A±N©Ò»Ý¸ê®Æ±a¤J
³o°¦µ{¦¡¨ä¹ê¬O°Ñ¦Òª©¥D¦b
http://forum.twbts.com/thread-12992-1-9.html
¸Ì­±¦^ÂЪºµ{¦¡¥[¥H­×§ïªº

¤À¨Éµ¹¦U¦ì¦³»Ý­nªºªO¤Í°Ñ¦Ò¨Ã½Ðµ¹¤©«ü±Ð
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("B1")) Is Nothing Or Target.Count > 1 Then '¦pªG§ó§ïªºÀx¦s®æ¤£¬OB¦C²Ä¤T¦æ¥H¤UªºÀx¦s®æ©Î§ó§ïªºÀx¦s®æ­Ó¼Æ¤j©ó1®É°h¥Xµ{¦¡
        Exit Sub
  End If
    Dim Rng, Ar, R As Range, ii As Integer, i As Integer
    Ar = Worksheets("®w¦sÁ`ªí").Range("B2").CurrentRegion.Value  '¸ê®Æ®w
     Set Rng = Range("A2", Range("A2").End(xlDown)).Resize(, 2)
    For Each R In Rng.Rows
        For ii = 1 To UBound(Ar, 1)
            If R.Cells(2, 1) & R.Cells(2, 2) = Ar(ii, 2) & Ar(ii, 3) Then '¶µ¥Ø1»P¶µ¥Ø2©M¸ê®Æ®w¶µ¥Ø2»P¶µ¥Ø3¤@¼Ë
                R.Cells(2, 7) = Ar(ii, 6) '¶µ¥Ø7¶ñ¤J¸ê®Æ®w¶µ¥Ø6¤º®e
                R.Cells(2, 9) = Ar(ii, 9) '¶µ¥Ø9¶ñ¤J¸ê®Æ®w¶µ¥Ø9¤º®e
                R.Cells(2, 6) = R.Cells(2, 5) * Range("B1") '¶µ¥Ø6(¥Í²£¥Î¶q)¶ñ¤J¶µ¥Ø5(°ò¥»¥Î¶q)*Àx¦s®æB1ªº­È
                R.Cells(2, 8) = R.Cells(2, 7) - R.Cells(2, 6) '¶µ¥Ø8(³Ñ¾l¼Æ¶q)=®w¦s¶q-¥Í²£¥Î¶q
               
                 If R.Cells(2, 8) < R.Cells(2, 9) Then '§â³Ñ¾l¼Æ¶q¸ò³Ì§C®w¦s¼Æ¶i¦æ¤ñ¹ï¡A­Y¬O¤p©ó
   
               R.Cells(2, 8).Font.Color = RGB(255, 0, 0) '¼Æ¦rÅܦ¨¬õ¦â
      
               Else
      
               R.Cells(2, 8).Font.Color = RGB(0, 0, 0) '¼Æ¦rÃC¦âºû«ù¤£ÅÜ
      
             End If
               
                Exit For
            End If
        Next
    Next
End Sub