Variables in Conditional Formatting Formula1

Question

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"")".

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.

The code is:

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

score 0 · Answer 1 · edited May 23 '17 at 11:49

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

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.

This is very helpful, thanks, for getting the last variable row and column to highlight the whole data area. But what I'd like is to put CM in a variable column or RM in a variable row and have that column or row highlighted in the data area. Is that possible? — johnn198, Aug 17 '14 at 06:34

Variables in Conditional Formatting Formula1

1 Answers1

Linked