Hsieh

回復 2# leirex1201


    基本上格式顏色無法使用基本功能選擇顏色
必須利用VBA輔助才能達成
Thisworkbook模組程式碼

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) = "顏色" Then
   
6.     Set A = Sheets("公司清單").[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("公司清單")
    
17. For Each Sh In Sheets
    
18.    If Sh.Name <> .Name Then
    
19.    Set A = Sh.Rows(1).Find("所屬公司", 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

複製代碼

求救.rar (25.76 KB)

下載次數: 11

2014-1-28 23:47