在我的表 (Range("A3:K9999")) 中有一列 E (E3:E9999)，其中包含下拉串列。
我的目標是在整個表格行中更改 bg 顏色，在該行中，用戶在 E 列中選擇下拉串列中的一項。在名為“Input”的第二個作業表中，是一個包含可選擇項的表格 (A4:A7)該串列框的專案。
到目前為止，這是我的代碼：
Sub Auto_Open() 'ConditionalFormatting
Dim rg As Range
Set rg = Worksheets(1).Range("A3:K9999")
'clear any existing conditional formatting
rg.FormatConditions.Delete
'define the formatting conditions
Dim cond1, cond2, cond3, cond4 As FormatCondition
Set cond1 = rg.FormatConditions.Add(xlExpression, Formula1:="=$E3=Input!A4")
Set cond2 = rg.FormatConditions.Add(xlExpression, Formula1:="=$E3=Input!A5")
Set cond3 = rg.FormatConditions.Add(xlExpression, Formula1:="=$E3=Input!A6")
Set cond4 = rg.FormatConditions.Add(xlExpression, Formula1:="=$E3=Input!A7")
'define the formatting applied for each condition
With cond1
.Interior.Color = vbYellow
End With
With cond2
.Interior.Color = vbRed
End With
With cond3
.Interior.Color = vbGreen
End With
With cond4
.Interior.Color = vbBlue
End With
End Sub
有了這個，當我打開 excel 檔案時，我只會在表格中看到一些瘋狂的顏色......主要是黃色。即使在串列框中沒有做出任何選擇。如何使此代碼在整個 E 列（E3：E9999）的每個下拉串列中正常作業，那個 Sub“Auto_Open（）”是正確的位置嗎？條件公式中是否存在參考錯誤？
uj5u.com熱心網友回復：
帶有資料驗證的 VBA 條件格式
Sub AddConditionalFormatting()
Const sName As String = "Input"
Const sAddress As String = "A4:A7"
Const dAddress As String = "A3:K9999"
Const dColumn As Long = 5
Dim dColors As Variant
dColors = VBA.Array(vbYellow, vbRed, vbGreen, vbBlue)
Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets("Input")
Dim srg As Range: Set srg = sws.Range(sAddress)
Dim dws As Worksheet: Set dws = ThisWorkbook.Worksheets(1) ' improve!
Dim drg As Range: Set drg = dws.Range(dAddress)
drg.FormatConditions.Delete
Dim sCell As Range
Dim r As Long
For Each sCell In srg.Cells
r = r 1
drg.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & drg.Cells(dColumn).Address(0) & "='" & sName _
& "'!" & srg.Cells(r).Address
drg.FormatConditions(drg.FormatConditions.Count) _
.Interior.Color = dColors(r - 1)
Next sCell
MsgBox "Conditional formatting added.", vbInformation
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/473436.html
下一篇：使用集合檢查名稱是否存在