- 帖子
- 1447
- 主題
- 40
- 精華
- 0
- 積分
- 1471
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-11-29
|
4#
發表於 2022-9-23 15:55
| 只看該作者
回復 3# coafort
謝謝前輩發表此主題
請前輩試試看
1.AO3:AO39格式設為yyyy/m/d;@
2.AO3設公式 =IF(AS3="","",TODAY())
3.下刷到AO39
 TEST_20220923-1.zip (25.09 KB)
今日習得=SUMPRODUCT()- Option Explicit
- Sub TEST_1()
- '[AS3:AS39]變更 觸發
- '讓[AO3:AO39]今天日期的AO底色不同,對應[BG46:BG82]底色不同也不同
- '只總計[AO3:AO39]是今日的,對應[BG46:BG82]數值在BG2
- Application.ScreenUpdating = False
- Dim i
- [BG2] = ""
- For i = 3 To 39
- If Cells(i, "AO") = Date Then
- Cells(i, "AO").Interior.ColorIndex = 17
- Cells(i, "BG").Item(44, 1).Interior.ColorIndex = 17
- [BG2] = [BG2] + Cells(i, "BG").Item(44, 1)
- Else
- Cells(i, "AO").Interior.ColorIndex = xlNone
- Cells(i, "BG").Item(44, 1).Interior.ColorIndex = xlNone
- End If
- Next
- End Sub
- Sub TEST_2()
- '[AS3:AS39]變更 觸發
- '讓[AO3:AO39]今天日期的AO底色不同,對應[BG46:BG82]底色不同也不同
- '只總計[AO3:AO39]是今日的,對應[BG46:BG82]數值在BG2
- Application.ScreenUpdating = False
- Dim i As Range, Y
- Set Y = CreateObject("Scripting.Dictionary")
- [BG2] = ""
- For Each i In Range([AO3], [AO82])
- Y.Add i, i.Item(44, 19)
- If i = Date Then
- i.Interior.ColorIndex = 35
- i.Item(44, 19).Interior.ColorIndex = 35
- [BG2] = [BG2] + Y(i)
- Else
- i.Interior.ColorIndex = xlNone
- i.Item(44, 19).Interior.ColorIndex = xlNone
- End If
- Next
- End Sub
- Sub TEST_3()
- '[AS3:AS39]變更 觸發
- '讓[AO3:AO39]今天日期的AO底色不同,對應[BG46:BG82]底色不同也不同
- '只總計[AO3:AO39]是今日的,對應[BG46:BG82]數值在BG2
- Application.ScreenUpdating = False
- Dim R&, Y, Z As Range, xR As Range
- Set Y = CreateObject("Scripting.Dictionary")
- [BG2] = ""
- Set Z = Range([AO3], [BG82])
- Z.Interior.ColorIndex = xlNone
- For Each xR In Z
- Y(xR.Value) = Y(xR.Value) + xR.Item(44, 19).Value
- If xR = Date Then
- xR.Interior.ColorIndex = 38
- xR.Item(44, 19).Interior.ColorIndex = 38
- End If
- Next
- [BG2] = Y(Date)
- End Sub
|
|
發表於 2022-9-24 16:07
| 