- 帖子
- 1447
- 主題
- 40
- 精華
- 0
- 積分
- 1471
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2025-2-11
|
回復 1# coafort
謝謝前輩發表此主題
VBA方法供參考
後學練習3種方法- Option Explicit
- Sub TEST_1()
- '讓今天日期的BA底色不同
- '只總計AO欄是今日的BA數值在BA2
- Application.ScreenUpdating = False
- Dim i
- [BA2] = ""
- For i = 3 To Cells(Rows.Count, "AO").End(xlUp).Row
- If Cells(i, "AO") = Date Then
- Cells(i, "AO").Interior.ColorIndex = 17
- [BA2] = [BA2] + Cells(i, "BA")
- Else
- Cells(i, "AO").Interior.ColorIndex = xlNone
- End If
- Next
- End Sub
- Sub TEST_2()
- '讓今天日期的BA底色不同
- '只總計AO欄是今日的BA數值在BA2
- Application.ScreenUpdating = False
- Dim i As Range, Y
- Set Y = CreateObject("Scripting.Dictionary")
- [BA2] = ""
- For Each i In Range([AO3], Cells(Rows.Count, "AO").End(3))
- Y.Add i, i.Item(1, 13)
- If i = Date Then
- i.Interior.ColorIndex = 17
- [BA2] = [BA2] + Y(i)
- Else
- i.Interior.ColorIndex = xlNone
- End If
- Next
- End Sub
- Sub TEST_3()
- '讓今天日期的BA底色不同
- '只總計AO欄是今日的BA數值在BA2
- Application.ScreenUpdating = False
- Dim R&, Y, Z As Range, xR As Range
- Set Y = CreateObject("Scripting.Dictionary")
- [BA2] = ""
- Set Z = Range([AO3], Cells(Rows.Count, "AO").End(3))
- Z.Interior.ColorIndex = xlNone
- For Each xR In Z
- Y(xR.Value) = Y(xR.Value) + xR.Item(1, 13).Value
- If xR = Date Then
- xR.Interior.ColorIndex = 17
- End If
- Next
- [BA2] = Y(Date)
- End Sub
|
|
發表於 2022-9-21 16:48
| 
