时间戳

首页 > 解决方案 > 如何在 FormatConditions.Add 方法的 xlExpression 中使用 LEFT 函数？

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

问题描述

我想在基于 LEFT(cell,1) 值的范围内设置条件格式。在此范围内，a 的值为“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

标签： excelvbaformattingconditional-formattinglistobject

解决方案

请尝试这种方式：

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

推荐阅读