leirex1201

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

lpk187

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

leirex1201

¦^´_  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À°À°¦£,ÁÂÁÂ

leirex1201

¦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¶Ü?ÁÂÁÂ

lpk187

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

¤U¸ü¦¸¼Æ: 7

2015-5-9 11:36

luhpro

½Ð±Ð¦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. 
   
7.   lRow = 2
   
8.   With Sheets("¤½¥q²M³æ")
   
9.     Do While .Cells(lRow, 1) <> ""
   
10.       vD(.Cells(lRow, 2).Text) = .Cells(lRow, 3).Interior.ColorIndex
    
11.       lRow = lRow + 1
    
12.     Loop
    
13.   End With
    
14.   
    
15.   lRow = 2
    
16.   With Sheets("³]³Æ¦Cªí")
    
17.     Do While .Cells(lRow, 1) <> ""
    
18.       .Rows(lRow).Interior.ColorIndex = vD(.Cells(lRow, 2).Text)
    
19.       lRow = lRow + 1
    
20.     Loop
    
21.   End With
    
22. End Sub

½Æ»s¥N½X

·íÀɮפº®e¦³§ó·s®É,ÂI¤@¤U "§ó·s©³¦â" «ö¶s§Y¥i.
[attach]20896[/attach]