使用 Excel VBA 代码的条件格式
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/13661965/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional Formatting using Excel VBA code
提问by Tejas
I have Range object called DistinationRange
which contains reference to range B3:H63
我有一个叫做 Range 对象DistinationRange
,它包含对 range 的引用B3:H63
I want to apply the following two conditional formatting rules using Excel VBA code dynamically. (Because the range would not be same all the time)
我想动态地使用 Excel VBA 代码应用以下两个条件格式规则。(因为范围不会一直相同)
- If Cell column D is blank, no formatting should be applied (Need to use Stop If True there)
- If Value in Cell of column E is lesser than value in cell of column F, that whole row should have green background.
- 如果单元格列 D 为空,则不应应用任何格式(需要在那里使用 Stop If True)
- 如果 E 列单元格中的值小于 F 列单元格中的值,则整行应具有绿色背景。
I tried a lot using recording but it's not recording properly.
我尝试了很多使用录音,但没有正确录音。
Kindly Help.
请帮助。
回答by Kevin Pope
This will get you to an answer for your simple case, but can you expand on how you'll know which columns will need to be compared (B and C in this case) and what the initial range (A1:D5
in this case) will be? Then I can try to provide a more complete answer.
这将为您提供简单案例的答案,但您能否扩展一下您将如何知道需要比较哪些列(在本例中为 B 和 C)以及初始范围(A1:D5
在本例中)是什么?然后我可以尝试提供更完整的答案。
Sub setCondFormat()
Range("B3").Select
With Range("B3:H63")
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($D3="""",FALSE,IF($F3>=$E3,TRUE,FALSE))"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
With .Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
End With
End With
End Sub
Note: this is tested in Excel 2010.
注意:这是在 Excel 2010 中测试的。
Edit: Updated code based on comments.
编辑:根据评论更新代码。
回答by Julius Getz M?rk
I think I just discovered a way to apply overlapping conditions in the expected way using VBA. After hours of trying out different approaches I found that what worked was changing the "Applies to" range for the conditional format rule, after every single one was created!
我想我刚刚发现了一种使用 VBA 以预期方式应用重叠条件的方法。经过数小时的尝试不同的方法后,我发现在创建每个方法后,更改条件格式规则的“适用于”范围是有效的!
This is my working example:
这是我的工作示例:
Sub ResetFormatting()
' ----------------------------------------------------------------------------------------
' Written by..: Julius Getz M?rk
' Purpose.....: If conditional formatting ranges are broken it might cause a huge increase
' in duplicated formatting rules that in turn will significantly slow down
' the spreadsheet.
' This macro is designed to reset all formatting rules to default.
' ----------------------------------------------------------------------------------------
On Error GoTo ErrHandler
' Make sure we are positioned in the correct sheet
WS_PROMO.Select
' Disable Events
Application.EnableEvents = False
' Delete all conditional formatting rules in sheet
Cells.FormatConditions.Delete
' CREATE ALL THE CONDITIONAL FORMATTING RULES:
' (1) Make negative values red
With Cells(1, 1).FormatConditions.add(xlCellValue, xlLess, "=0")
.Font.Color = -16776961
.StopIfTrue = False
End With
' (2) Highlight defined good margin as green values
With Cells(1, 1).FormatConditions.add(xlCellValue, xlGreater, "=CP_HIGH_MARGIN_DEFINITION")
.Font.Color = -16744448
.StopIfTrue = False
End With
' (3) Make article strategy "D" red
With Cells(1, 1).FormatConditions.add(xlCellValue, xlEqual, "=""D""")
.Font.Bold = True
.Font.Color = -16776961
.StopIfTrue = False
End With
' (4) Make article strategy "A" blue
With Cells(1, 1).FormatConditions.add(xlCellValue, xlEqual, "=""A""")
.Font.Bold = True
.Font.Color = -10092544
.StopIfTrue = False
End With
' (5) Make article strategy "W" green
With Cells(1, 1).FormatConditions.add(xlCellValue, xlEqual, "=""W""")
.Font.Bold = True
.Font.Color = -16744448
.StopIfTrue = False
End With
' (6) Show special cost in bold green font
With Cells(1, 1).FormatConditions.add(xlCellValue, xlNotEqual, "=0")
.Font.Bold = True
.Font.Color = -16744448
.StopIfTrue = False
End With
' (7) Highlight duplicate heading names. There can be none.
With Cells(1, 1).FormatConditions.AddUniqueValues
.DupeUnique = xlDuplicate
.Font.Color = -16383844
.Interior.Color = 13551615
.StopIfTrue = False
End With
' (8) Make heading rows bold with yellow background
With Cells(1, 1).FormatConditions.add(Type:=xlExpression, Formula1:="=IF($B8=""H"";TRUE;FALSE)")
.Font.Bold = True
.Interior.Color = 13434879
.StopIfTrue = False
End With
' Modify the "Applies To" ranges
Cells.FormatConditions(1).ModifyAppliesToRange Range("O8:P507")
Cells.FormatConditions(2).ModifyAppliesToRange Range("O8:O507")
Cells.FormatConditions(3).ModifyAppliesToRange Range("B8:B507")
Cells.FormatConditions(4).ModifyAppliesToRange Range("B8:B507")
Cells.FormatConditions(5).ModifyAppliesToRange Range("B8:B507")
Cells.FormatConditions(6).ModifyAppliesToRange Range("E8:E507")
Cells.FormatConditions(7).ModifyAppliesToRange Range("A7:AE7")
Cells.FormatConditions(8).ModifyAppliesToRange Range("B8:L507")
ErrHandler:
Application.EnableEvents = False
End Sub