VBA条件格式 [英] VBA Conditional Formatting

问题描述

我正在努力寻找一种通过VBA应用条件格式设置规则的优雅方法。我更喜欢VBA，因为a）规则将适用于多个工作表，并且b）防止在工作表之间复制/粘贴时出现CF复制问题。

我有一个列表库存物品，所有存放在不同的位置。我想根据位置使用以下格式进行格式化：

字体颜色（每个位置都会改变）；顶部边框（与字体颜色相同）；底部边框（与字体颜色相同）

此外，该范围必须是动态的，因为每个工作表都适用于该表。我想将相同的代码应用于每个适用的工作表，而不需要为每个工作表的表名进行硬编码。

任何帮助将不胜感激。 / p>

---

-UPDATE--
我试图适应J_V的代码此处，但在公共订阅服务器的 r上收到运行时错误'5'：无效的过程调用或参数 .FormatConditions.Add类型：= xlExpression，公式1：=公式。我不确定边界的最后一位是否正确，因为运行时会停止宏。我还仍然需要处理动态表引用，但是一次只能处理一个问题。

  Sub ConditionalFormatting（） 
 
 Dim myRange作为范围
 Set myRange = ThisWorkbook.Sheets（ Widget1）。Range（ Widget1_table [Location]）
 
 myRange.FormatConditions.Delete 
 
调用FormatRange（myRange，10， = $ E5 = Warehouse1）
调用FormatRange（myRange，11， = $ E5 = Warehouse2）
调用FormatRange （myRange，13， = $ E5 = Warehouse3）
 
 End Sub 
 
 Public Sub FormatRange（r作为范围，颜色作为整数，公式作为字符串）
 r.FormatConditions.Add类型：= xlExpression，Formula1：=公式
 r.FormatConditions（r.FormatConditions.Count）.Font.colorindex = color 
 
与r.FormatConditions（1 ）.Borders（xlTop）
 .LineStyle = xlContinuous 
 .Color = color 
 .TintAndShade = 0 
 .Weight = xlThin 
 End with 
 With r .FormatConditions（1）.Borders（xlBottom）
 .LineStyle = xlContinuous 
 .Color = color 
 .TintAndShade = 0 
 .Weight = xlThin 
以
结尾r.FormatConditions（1）.StopIfTrue = False 
 
 End Sub 
  

解决方案

问题实际上不在 Sub FormatRange之内，但您在 Sub ConditionalFormatting 中调用公式时会采用这种方式。公式包含一个字符串，因此必须像这样将引号加倍。

  Sub ConditionalFormatting（）
 Dim myRange作为范围
设置myRange = ThisWorkbook.Sheets（ Widget1）。Range（ Widget1_table [Location]）
 
 myRange.FormatConditions.Delete 
 
调用FormatRange （myRange，10， = $ E5 = Warehouse1）
调用FormatRange（myRange，11， = $ E5 = Warehouse2）
调用FormatRange（myRange，13 ， = $ E5 = Warehouse3）
结束子
  

第二个宏，当您添加新条件时，它进入队列的底部。如果您查看创建CF规则的记录输出，将会看到它通常包含以下行：

  Selection.FormatConditions（ Selection.FormatConditions.Count）.SetFirstPriority 
  

这会将CF规则放在队列的顶部，以便此后可以称为 .FormatConditions（1）。如果您不希望它在队列的顶部，则必须将其称为队列中的 last 。

 公共子FormatRange（r作为范围，clr作为整数，frml作为字符串）
 r.FormatConditions.Add类型：= xlExpression，Formula1：= frml 
 r.FormatConditions （r.FormatConditions.Count）.Font.ColorIndex = clr 
 
与r.FormatConditions（r.FormatConditions.Count）.Borders（xlTop）
 .LineStyle = xlContinuous 
。 ColorIndex = clr 
 .TintAndShade = 0 
 .Weight = xlThin 
以
结尾，带有r.FormatConditions（r.FormatConditions.Count）.Borders（xlBottom）
。 LineStyle = xlContinuous 
 .ColorIndex = clr 
 .TintAndShade = 0 
 .Weight = xlThin 
以
结尾r.FormatConditions（r.FormatConditions.Count）.StopIfTrue =错误
结束子
  

我还更改了边框。颜色分配给 .ColorIndex 为 10，11 & 13 似乎是* green，蓝色和紫色的 ColorIndex 标识符。将元音从变量名中删除，以避免与集合属性的名称冲突。

I am struggling to figure out an elegant way to apply conditional formatting rules via VBA. I prefer VBA because a) the rules will apply to multiple worksheets, and b) it prevents the CF duplication problem when copying/pasting between worksheets.

I have a list of inventory items, all kept in different locations. I want to format based on location with the following formatting:

Font color (will change for each location); Top border (same color as font); Bottom border (same color as font)

Also, the range needs to be dynamic in that for each worksheet, it applies to the table on that sheet. I would like to apply the same code to each applicable worksheet, rather than needing to hard-code the table name for each worksheet.

Any help would be greatly appreciated.

---

--UPDATE-- I tried to adapt J_V's code here but receive a "Run-time error '5': Invalid procedure call or argument" on the Public Sub's r.FormatConditions.Add Type:=xlExpression, Formula1:=formula. I'm unsure if the last bit on borders is correct since the run-time stops the macro. I also still need to work in dynamic table references, but I'm working one issue at a time.

Sub ConditionalFormatting()

Dim myRange As Range
Set myRange = ThisWorkbook.Sheets("Widget1").Range("Widget1_table[Location]")

myRange.FormatConditions.Delete

Call FormatRange(myRange, 10, "=$E5="Warehouse1")
Call FormatRange(myRange, 11, "=$E5="Warehouse2")
Call FormatRange(myRange, 13, "=$E5="Warehouse3")

End Sub

Public Sub FormatRange(r As Range, color As Integer, formula As String)
r.FormatConditions.Add Type:=xlExpression, Formula1:=formula
r.FormatConditions(r.FormatConditions.Count).Font.colorindex = color

With r.FormatConditions(1).Borders(xlTop)
    .LineStyle = xlContinuous
    .Color = color
    .TintAndShade = 0
    .Weight = xlThin
End With
With r.FormatConditions(1).Borders(xlBottom)
    .LineStyle = xlContinuous
    .Color = color
    .TintAndShade = 0
    .Weight = xlThin
End With
r.FormatConditions(1).StopIfTrue = False

End Sub

解决方案

The problem is not actually within Sub FormatRange but in the way you are assigning the formula when calling it within Sub ConditionalFormatting. The formula contains a string so the quotes have to be doubled up like this.

Sub ConditionalFormatting()
    Dim myRange As Range
    Set myRange = ThisWorkbook.Sheets("Widget1").Range("Widget1_table[Location]")

    myRange.FormatConditions.Delete

    Call FormatRange(myRange, 10, "=$E5=""Warehouse1""")
    Call FormatRange(myRange, 11, "=$E5=""Warehouse2""")
    Call FormatRange(myRange, 13, "=$E5=""Warehouse3""")
End Sub

As to the second macro, when you add a new condition it goes to the bottom of the queue. If you look at the recorded output from creating a CF rule you will see that it typically contains the line,

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

This puts the CF rule at the top of the queue so that it can thereafter be referred to as .FormatConditions(1). If you do not want it at the top of the queue then you have to refer to it as the last in the queue like this.

Public Sub FormatRange(r As Range, clr As Integer, frml As String)
    r.FormatConditions.Add Type:=xlExpression, Formula1:=frml
    r.FormatConditions(r.FormatConditions.Count).Font.ColorIndex = clr

    With r.FormatConditions(r.FormatConditions.Count).Borders(xlTop)
        .LineStyle = xlContinuous
        .ColorIndex = clr
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With r.FormatConditions(r.FormatConditions.Count).Borders(xlBottom)
        .LineStyle = xlContinuous
        .ColorIndex = clr
        .TintAndShade = 0
        .Weight = xlThin
    End With
    r.FormatConditions(r.FormatConditions.Count).StopIfTrue = False
End Sub

I also changed your border .Color assignments to .ColorIndex as 10, 11 & 13 seem to be ColorIndex identifiers for *green, blue and purple. The vowels were removed from your variable names to avoid conflict with the names of collection properties.

这篇关于VBA条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！