如何在FormatConditions.Add方法中的xlExpression中使用LEFT函数

Question

我想在基于LEFT(cell，1(值的范围内设置条件格式。在这个范围内a具有一个值"0"；1A〃；如果第一个字符LEFT((等于1，我想把它画成红色。我的代码没有任何作用。我怎样才能做到这一点？

我的代码

Sub ConditionalFormatting()
Dim condition1 As FormatCondition, condition2 As FormatCondition

Dim tbl As ListObject
Set tbl = ThisWorkbook.Worksheets("Overview").ListObjects("Column_Overview")

Dim tblLastColumn As Integer
tblLastColumn = tbl.Range.Columns.Count

'Fixing/Setting the range on which conditional formatting is to be desired
Dim rng As Range
Set rng = tbl.ListColumns(8).DataBodyRange.Resize(, tblLastColumn - 7)

'Defining and setting the criteria for each conditional format

Set condition1 = rng.FormatConditions.Add(Left(xlCellValue, 1), xlEqual, "=1")
Set condition2 = rng.FormatConditions.Add(xlCellValue, xlLess, "=50")
'Defining and setting the format to be applied for each condition
With condition1
.Font.Color = vbRed
.Font.Bold = True
End With

Answer 1

请尝试这种方式：

Sub testFormatCondLeft()
Dim condition1 As FormatCondition
Dim condition1 As FormatCondition, rng As Range

'your existing code...
Set rng = tbl.ListColumns(8).DataBodyRange.Resize(, tblLastColumn - 7)
With rng
.FormatConditions.Delete
Set condition1 = .FormatConditions.Add(Type:=xlExpression, Formula1:="=Left(" & .cells(1).Address(0, 0) & ", 1)=""1""")
End With
With condition1
.Font.Color = vbRed
.Font.Bold = True
End With
End Sub