条件格式公式1中的变量 [英] Variables in Conditional Formatting Formula1

问题描述

我正在尝试在FormatCondition Formula1属性中使用变量.变量将是单元格引用.但是，我无法正确使用语法.我在下面的代码中遇到的两个问题是:"=(C$3:J$10=""CM"")"和"=($C3:$J10=""RM"")".

I'm trying to use variables in the FormatCondition Formula1 property. The variables will be cell references. However, I can't get the syntax right. The two bits I'm having trouble with in the code below are: "=(C$3:J$10=""CM"")" and "=($C3:$J10=""RM"")".

此操作的目的是在特定单元格中突出显示带有CM的列，并在特定单元格中突出显示带有RM的行.列和行的数量将增加或减少，因此将使用变量.

The aim of this is to highlight a column with CM in a certain cell, and to highlight a row with RM in a certain cell. The number of columns and rows will increase and decrease, hence the use of variables.

或者，如果这不是正确的方法或最佳方法，则不胜感激.

Or if this isn't the right way or the best way, alternatives would be appreciated.

代码是:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Rows
Dim iRowA As Integer, iRowB As Integer, iRowC As Integer
Dim iRowDataStart As Integer, iRowLast As Integer
'Columns
Dim iColX As Integer, iColY As Integer, iColZ As Integer
Dim iColDataStart As Integer, iColLast As Integer
'Ranges
Dim rAll As Range
Dim rRowB As Range, rColY As Range
Dim rRowMark As Range, rColMark As Range
'String
Dim sString As String
'Assign values, normally these would be variable values, not assigned
iRowA = 1: iRowB = 2: iRowC = 3
iRowDataStart = 4: iRowLast = 10
iColX = 1: iColY = 2: iColZ = 3
iColDataStart = 4: iColLast = 10
'Set ranges
Set rAll = Range(Cells(iRowA, iColX), Cells(iRowLast, iColLast))
Set rRowB = Range(Cells(iRowB, iColZ), Cells(iRowLast, iColLast))
Set rColY = Range(Cells(iRowC, iColY), Cells(iRowLast, iColLast))
Set rRowMark = Range(Cells(iRowC, iColZ), Cells(iRowLast, iColLast))
Set rColMark = Range(Cells(iRowC, iColZ), Cells(iRowLast, iColLast))
'Delete all CF currently in the worksheet
With rAll
    .FormatConditions.Delete
End With
'Format column with Column Mark
sString = "=(C$3:J$10=""CM"")"
With rRowB
    .FormatConditions.Add _
        Type:=xlExpression, _
        Formula1:=sString
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1)
        .Interior.Color = RGB(196, 189, 151)
        .StopIfTrue = False
    End With
End With
'Format row with Row Mark
sString = "=($C3:$J10=""RM"")"
With rColY
    .FormatConditions.Add _
        Type:=xlExpression, _
        Formula1:=sString
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1)
        .Font.ColorIndex = 2
        .Interior.Color = RGB(127, 127, 127)
        .StopIfTrue = False
    End With
End With
Range("A1").Select
Application.StatusBar = False
Application.CutCopyMode = False
End Sub

推荐答案

您只需要通过获取数据的最后一行和最后一列来动态设置范围，即可在此处找到许多示例

You just need to dynamically set your ranges by getting last row and column of your data where you can find many examples here like this one. Something like:

Dim r As Range
Dim lr As Long, lc As Long
Dim formula As String

With Sheet1 '~~> change to your actual sheet
    lr = .Range("C" & .Rows.Count).End(xlUp).Row '~~> based on C, adjust to suit
    lc = .Cells(3, .Columns.Count).End(xlToLeft).Column '~~> based on row 3
    Set r = .Range(.Cells(3, 3), .Cells(lr, lc))
    formula = "=(" & r.Address & "=""CM"")"
    '~~> formatting code here
End With

或者您可以尝试我在此处发布的有关 有条件的内容格式化 ，当我发布 HERE .像这样:

Or you can try what I've posted here about Conditional Formatting which of course can be automated as I posted HERE and HERE. Something like:

formula = "=C3=""CM"""
[C3].FormatConditions.Add xlExpression, , formula
With [C3].FormatConditions(1)
    .Interior.Color = RGB(196, 189, 151)
    .ModifyAppliesToRange r
End With

HTH.

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