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

[µo°Ý] ½Ð¤j®v­ÌÀ°¦£¦Û°ÊÅÜ´«ÃC¦â,ÁÂÁÂ

[µo°Ý] ½Ð¤j®v­ÌÀ°¦£¦Û°ÊÅÜ´«ÃC¦â,ÁÂÁÂ

½Ð±Ð¦U¦ì¤j®v

§Ú·Q­n¨Ì"¤½¥q²M³æ"³o­Ósheet¸Ì¦Cªº¤½¥q,Åý"³]³Æ¦Cªí"³o­Ósheet¸Ì­±ªº³]³Æ¦Û°Ê¾ã¦CÅܦ⦨¬°"¤½¥q²M³æ"sheet¤º¹ïÀ³ªºÃC¦â
¦]¥»¨­¹ïÃC¦â¿ëÃѯà¤O¸û®z,¨ä±æ¥i¥H¦³¬ÛÀ³ªº¦â½X¥H¨Ñ¿ëÃÑ
§Æ±æ¯à¬Û®e©óexcel 2003~2010
½Ð¦U¦ì¤j®vÀ°¦£,·PÁÂ
´ú¸ÕÀÉ®×.zip (3.97 KB)
¤Ñ¤ÑªÅªÅ°Ú~

½Ð±Ð¦U¦ì¤j®v

§Ú·Q­n¨Ì"¤½¥q²M³æ"³o­Ósheet¸Ì¦Cªº¤½¥q,Åý"³]³Æ¦Cªí"³o­Ósheet¸Ì­±ªº³]³Æ¦Û°Ê¾ã¦CÅܦ⦨¬° ...
leirex1201 µoªí©ó 2015-5-8 18:27

¦L¶H¤¤¦b Excel 2003 ¦ü¥G¨S¦³¥iª½±µ§ì¨úÀx¦s®æ©³¦âªº¤½¦¡.

§Aªº»Ý¨D¥Î Excel VBA ¸û®e©ö¹F¦¨:
  1. Private Sub cbGetColor_Click()
  2.   Dim lRow&
  3.   Dim vD
  4.   
  5.   Set vD = CreateObject("Scripting.Dictionary")

  6.   lRow = 2
  7.   With Sheets("¤½¥q²M³æ")
  8.     Do While .Cells(lRow, 1) <> ""
  9.       vD(.Cells(lRow, 2).Text) = .Cells(lRow, 3).Interior.ColorIndex
  10.       lRow = lRow + 1
  11.     Loop
  12.   End With
  13.   
  14.   lRow = 2
  15.   With Sheets("³]³Æ¦Cªí")
  16.     Do While .Cells(lRow, 1) <> ""
  17.       .Rows(lRow).Interior.ColorIndex = vD(.Cells(lRow, 2).Text)
  18.       lRow = lRow + 1
  19.     Loop
  20.   End With
  21. End Sub
½Æ»s¥N½X
·íÀɮפº®e¦³§ó·s®É,ÂI¤@¤U "§ó·s©³¦â" «ö¶s§Y¥i.
[attach]20896[/attach]

´ú¸ÕÀÉ®×-a.zip (11.89 KB)

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-5-9 11:38 ½s¿è

¦^´_ 1# leirex1201

¤]¥i¥H¥Î¤u§@ªí¨Æ¥ó¨Ó¹F¦¨¦Û°Ê¤Æ
·íBÄæ¥H«á¨ìEÄ榳ÅܤƮɴN·|¹F¦¨¥Øªº
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Target.Row > 1 And Target.Column < 6 And Cells(Target.Row, 2) <> "" Then
  3.     Set col = Sheets("¤½¥q²M³æ").Columns(2).Find(Cells(Target.Row, 2), , , , , 2)
  4.     Range(Cells(Target.Row, 1), Cells(Target.Row, 1).End(xlToRight)).Interior.Color = _
  5.     col.Offset(0, 1).Interior.Color
  6. End If
  7. End Sub
½Æ»s¥N½X
´ú¸ÕÀÉ®×.rar (14.21 KB)

TOP

¦L¶H¤¤¦b Excel 2003 ¦ü¥G¨S¦³¥iª½±µ§ì¨úÀx¦s®æ©³¦âªº¤½¦¡.

§Aªº»Ý¨D¥Î Excel VBA ¸û®e©ö¹F¦¨:·íÀɮפº ...
luhpro µoªí©ó 2015-5-9 06:55



·PÁÂluhpro¤j®v¦^ÂÐ

½Ð°Ý­n¶K¨ìwookbook¶Ü?ÁÂÁÂ
¤Ñ¤ÑªÅªÅ°Ú~

TOP

¥»©«³Ì«á¥Ñ leirex1201 ©ó 2015-5-11 10:07 ½s¿è
¦^´_  leirex1201

¤]¥i¥H¥Î¤u§@ªí¨Æ¥ó¨Ó¹F¦¨¦Û°Ê¤Æ
·íBÄæ¥H«á¨ìEÄ榳ÅܤƮɴN·|¹F¦¨¥Øªº
lpk187 µoªí©ó 2015-5-9 11:36



·PÁÂlpk187¤j®v¦^ÂÐ

If Target.Row > 1 And Target.Column < 10 And Cells(Target.Row, 1) <> "" Then
¥H¤W¬Ochangeªº¨Æ¥ó½d³ò¶Ü
Range(Cells(Target.Row, 1), Cells(Target.Row, 1).End(xlToRight)).Interior.Color = _
    col.Offset(0, 1).Interior.Color
¦ý§Ú¤£À´³o­Ó,
§Ú·Q­n³]©wÅܦ⪺½d³ò,¥u­nBÄæ¦ì¦³­È,¤£ºÞ«á­±ªºÄæ¦ì¦³¨S¦³­È,¤ñ¦pA10~P10¥þ³¡Åܦâ,½Ð¤j®vÀ°À°¦£,ÁÂÁÂ
¤Ñ¤ÑªÅªÅ°Ú~

TOP

¦^´_ 5# leirex1201
¥u¦³BÄ檺¸Ü¥i¥H§ï¦¨³o¼Ë
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Target.Address = Cells(Target.Row, "B").Address Then
  3.     Set col = Sheets("¤½¥q²M³æ").Columns(2).Find(Cells(Target.Row, 2), , , , , 2)
  4.     Range(Cells(Target.Row, 1), Cells(Target.Row, 5)).Interior.Color = _
  5.     col.Offset(0, 1).Interior.Color
  6. End If
  7. End Sub
½Æ»s¥N½X

TOP

¦^´_  leirex1201
¥u¦³BÄ檺¸Ü¥i¥H§ï¦¨³o¼Ë
lpk187 µoªí©ó 2015-5-11 10:24


·PÁÂlpk187¤j¤j¦A¤@¦¸À°¦£

¥i¯à¬O§Ú»¡©úªº¤£²M·¡
§Úªº·N«ä¬OA2¦³­È"1",B2¦³­È"»·¶Ç¹q«H",¦ÓC2:K2³£¬OªÅ¥Õ,¦ý§Ú·Q§âA2:K2¥þ³¡Åܦâ,·PÁ¤j®v
¤Ñ¤ÑªÅªÅ°Ú~

TOP

¦^´_ 7# leirex1201

¶â¡I¨º´N§ï¦¨¤U¦C
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Target.Column < 3 And Target.Row > 1 And Cells(Target.Row, "B") <> "" And Cells(Target.Row, "A") <> "" Then
  3.     Set col = Sheets("¤½¥q²M³æ").Columns(2).Find(Cells(Target.Row, 2), , , , , 2)
  4.     Range(Cells(Target.Row, 1), Cells(Target.Row, 5)).Interior.Color = _
  5.     col.Offset(0, 1).Interior.Color
  6. End If
  7. End Sub
½Æ»s¥N½X

TOP

¦^´_ 8# lpk187

§ï¦¨¤U¦Cªº¸Ü¡A¥u­nAÄæ©MBÄæ¨ä¤¤¤@­ÓµL­Èªº¸Ü¦^´_ªÅ¥Õ
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Target.Column < 3 And Target.Row > 1 And Cells(Target.Row, "B") <> "" And Cells(Target.Row, "A") <> "" Then
  3.     Set col = Sheets("¤½¥q²M³æ").Columns(2).Find(Cells(Target.Row, 2), , , , , 2)
  4.         AA = col.Offset(0, 1).Interior.Color
  5.     Range(Cells(Target.Row, 1), Cells(Target.Row, 5)).Interior.Color = _
  6.     col.Offset(0, 1).Interior.Color
  7. Else
  8.     Range(Cells(Target.Row, 1), Cells(Target.Row, 5)).Interior.Color = 16777215
  9. End If
  10. End Sub
½Æ»s¥N½X

TOP

¦^´_  lpk187

§ï¦¨¤U¦Cªº¸Ü¡A¥u­nAÄæ©MBÄæ¨ä¤¤¤@­ÓµL­Èªº¸Ü¦^´_ªÅ¥Õ
lpk187 µoªí©ó 2015-5-11 11:41


·PÁ lpk187 ¤j®v¦A¦¸À°¦£

¤£¦n·N«ä,§Ú¥i¯àÁÙ¬O¨Sªí¹F²M·¡,§Ú°µ¤F¹Ï¤ù¦p¤U
§Ú·Q­n¹F¦¨¦p¤U¹Ï³o¼Ë¤l,¤£ºÞ§Ú¦b­þ¤@column½s¿è,¥u­ncolumn(B)ªº­È¬O²Å¦X"¤½¥q²M³æ"sheetªº­È,¨ºrow(A2:K10)³£Åܦâ
¦A¦¸·PÁÂ^^
¤Ñ¤ÑªÅªÅ°Ú~

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD