|
- Sub 分配颜色()
- Dim N, N1
- N = 6: N1 = 6 '分别为原品种数,新增品种数(自己定义)
- arr = Range("a3").Resize(N, 2) '已用颜色
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- For i = 1 To UBound(arr)
- d(arr(i, 2)) = ""
- d1(arr(i, 1)) = arr(i, 2)
- Next
-
- brr = Range("c3").Resize(N1, 2) '新增产品
- ys = Range("G3:G10") '所有颜色库
- For i = 1 To UBound(brr)
- If d1.exists(brr(i, 1)) Then
- brr(i, 2) = d1(brr(i, 1))
- Else
- For j = 1 To UBound(ys)
- If Not d.exists(ys(j, 1)) Then
- d(ys(j, 1)) = ""
- brr(i, 2) = ys(j, 1)
- Exit For
- End If
- Next
- If j = UBound(ys) + 1 Then brr(i, 2) = "没有可用颜色"
- End If
- Next
- Range("c3").Resize(N1, 2) = brr
- End Sub
复制代码 |
|