Sub SetConditionalFormat() Dim rng As Range Dim col As Long Dim lastVal As Variant ' 获取已选定的范围 Set rng = Selection ' 遍历每一列 For col = 1To rng.Columns.Count ' 获取每列最后一个单元格的数值 lastVal = rng.Cells(rng.Rows.Count, col).Value ' 如果是数字,则设置条件格式 If IsNumeric(lastVal) Then ' 添加条件格式规则:如果小于最后一个数值,则设置字体为红色 rng.Columns(col).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=lastVal rng.Columns(col).FormatConditions(1).Font.Color = vbRed EndIf Next col EndSub
Sub SetConditionalFormat() Dim rng As Range Dim col As Variant Dim lastVal As Variant ' 获取已选定的范围 Set rng = Selection ' 定义一个数组,存储要设置条件格式的列号 Dim colsArray(1To4) As Variant colsArray(1) = 9 colsArray(2) = 12 colsArray(3) = 15 colsArray(4) = 18 ' 遍历数组中的每个列号 ForEach col In colsArray ' 获取每列最后一个单元格的数值 lastVal = rng.Cells(rng.Rows.Count, col).Value ' 如果是数字,则设置条件格式 If IsNumeric(lastVal) Then ' 添加条件格式规则:如果小于最后一个数值,则设置字体为红色 rng.Columns(col).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=lastVal rng.Columns(col).FormatConditions(1).Font.Color = vbRed EndIf Next col EndSub