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

[µo°Ý] ¨D±Ï¦Û°Ê¿ï¦â¤Î¶ñ¦â~

[µo°Ý] ¨D±Ï¦Û°Ê¿ï¦â¤Î¶ñ¦â~

½Ð¦U¦ì±M®aÀ°À°¦£~¸U¤À·PÁÂ
¨D±Ï.zip (15.2 KB)
¤Ñ¤ÑªÅªÅ°Ú~

½Ð¦U¦ì±M®aÀ°¦£«ü¾É¤@¤U~·PÁÂ
¤Ñ¤ÑªÅªÅ°Ú~

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2014-1-28 23:47 ½s¿è

¦^´_ 2# leirex1201


    °ò¥»¤W®æ¦¡ÃC¦âµLªk¨Ï¥Î°ò¥»¥\¯à¿ï¾ÜÃC¦â
¥²¶·§Q¥ÎVBA»²§U¤~¯à¹F¦¨
Thisworkbook¼Ò²Õµ{¦¡½X
  1. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  2. Dim A As Range
  3. Application.EnableEvents = False
  4. With Target
  5.   If Sh.Cells(1, .Column) = "ÃC¦â" Then
  6.     Set A = Sheets("¤½¥q²M³æ").[H:H].Find(Target, lookat:=xlWhole)
  7.     If Not A Is Nothing Then _
  8.     .Interior.ColorIndex = A.Interior.ColorIndex: .Offset(, 1) = A.Offset(, -1)
  9.   End If
  10.   DrowCell
  11. End With
  12. Application.EnableEvents = True
  13. End Sub
  14. Sub DrowCell()
  15. Dim A As Range, C As Range
  16. With Sheets("¤½¥q²M³æ")
  17. For Each Sh In Sheets
  18.    If Sh.Name <> .Name Then
  19.    Set A = Sh.Rows(1).Find("©ÒÄݤ½¥q", lookat:=xlWhole)
  20.    For Each C In Sh.Range(A.Offset(1, 0), A.End(xlDown))
  21.       If Not .[C:C].Find(C, lookat:=xlWhole) Is Nothing Then _
  22.       C.Interior.ColorIndex = .[C:C].Find(C, lookat:=xlWhole).Offset(, 1).Interior.ColorIndex _
  23.       Else C.Interior.ColorIndex = xlNone
  24.    Next
  25.    End If
  26. Next
  27. End With
  28. End Sub
½Æ»s¥N½X
¨D±Ï.rar (25.76 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¯u¬O«D±`·PÁªO¥DªºÀ°¦£,«ö·ÓªO¥Dªºµ{¦¡¯uªº¥i¥HÅܦâ,¦ý¬O¨S¦³¾ã¦CÅܦâ~½ÐªO¥D¦A«ü¾É¤@¤U,ÁÂÁÂ
¤Ñ¤ÑªÅªÅ°Ú~

TOP

¸Õ¸Õ¬Ý
  1. Sub «e´ºÃC¦â¥N¸¹_Change()
  2.    ActiveCell = Range("D2")
  3.    ActiveCell.Offset(, -1).Font.ColorIndex = ActiveCell
  4. End Sub

  5. Sub ­I´ºÃC¦â¥N¸¹_Change()
  6.    ActiveCell = Range("E2")
  7.    ActiveCell.Offset(, -2).Interior.ColorIndex = ActiveCell
  8. End Sub

  9. Private Sub cmd¶ñ¦â_Click()
  10.    'ActiveCell ¶·¦b Äæ10 ©M Äæ16 ¤§¶¡
  11.    '«ö¶s cmd¶ñ¦â ¤~¦³§@¥Î
  12.    col1 = ActiveCell.Column
  13.    If col1 < 10 Or col1 > 16 Then Exit Sub
  14.    
  15.    °_ÂI = ActiveCell.Row
  16.    ²×ÂI = Cells(°_ÂI, 13).End(xlDown).Row
  17.    
  18.    'cÄæ ¤½¥q¥Nªí¦WºÙ ªºÁ`¼Æ
  19.    c¥N¸¹Á`¼Æ = [C3].End(xlDown).Row
  20.    
  21.    For mRow = °_ÂI To ²×ÂI
  22.       m¥N¸¹ = UCase(Cells(mRow, 13))
  23.       For cRow = 3 To c¥N¸¹Á`¼Æ
  24.          c¥N¸¹ = Cells(cRow, 3)
  25.          If m¥N¸¹ = c¥N¸¹ Then
  26.             «e´º = Cells(cRow, 4)
  27.             ­I´º = Cells(cRow, 5)
  28.             Cells(mRow, 10).Resize(, 7).Select
  29.             Selection.Interior.ColorIndex = ­I´º
  30.             Selection.Font.ColorIndex = «e´º
  31.             Exit For
  32.          End If
  33.       Next
  34.    Next
  35. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# yen956

·PÁÂyen956¤j¤jªºÀ°¦£
¤Ñ¤ÑªÅªÅ°Ú~

TOP

        ÀR«ä¦Û¦b : ¯u¥¿ªº·R¤ß¡A¬O·ÓÅU¦n¦Û¤vªº³oÁû¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD