How to Pass Arguments in a Formula?

[tinfanide]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Dim oSchoolWorkTable As ListObject, sSchoolWorkTable As String
Set oSchoolWorkTable = ListObjects("SchoolWorkTable")
sSchoolWorkTable = oSchoolWorkTable.Name & "[#This Row]"

With oSchoolWorkTable.ListColumns("Name").Range.FormatConditions _
'''
    .Add(xlExpression, Formula1:="=COUNTIF(INDIRECT("" & sSchoolWorkTable & ""),FALSE)>0")
'''
    .Interior.Color = RGB(255, 199, 206)
    .Font.Color = RGB(156, 0, 6)
End With

Application.EnableEvents = True
End Sub

I wonder how I could pass the argument in the INDIRECT function.


Thank you.

[tinfanide]

I've resolved it myself.

[tinfanide]

I've resolved it myself.

Dim sIndirect As String

sIndirect = "INDIRECT(""" & oSchoolWorkTable.Name & "[#This Row]"")"

With oSchoolWorkTable.ListColumns("Name").Range.FormatConditions _
    .Add(xlExpression, Formula1:="=COUNTIF(" & sIndirect & ",FALSE)>0")
End With