如何使用VBA代码的条件格式 [英] How do I use conditional formatting with VBA codes

问题描述

从我的数据中我希望红色单元格的值小于50％琥珀色，介于50-90之间，绿色大于90％。

当我运行此程序时显示语法错误

使用列（D2：D37）。FormatConditions.Add类型：= xlExpression，运算符：= xlBetween，_

Formula1：== 50，Formula2：= = 90



我尝试过：

from my data i want red cells for values less than 50% amber for between 50-90 and green for greater than 90%.
when i run this program its showing syntax error for
With Columns("D2:D37").FormatConditions.Add Type:=xlExpression, Operator:=xlBetween,_
Formula1:="=50", Formula2:="=90"

What I have tried:

Sub test()
Worksheets("sheet4").Activate


With Columns("D2:D37").FormatConditions.Add Type:=xlExpression, Operator:=xlBetween,_
        Formula1:="=50", Formula2:="=90"
    With .Item(.Count).Interior
        .Color = 49407
    End With
     Selection.FormatConditions.Add Type:=xlExpression, Operator:=xlGreater, _
        Formula1:="=90"
    With .Item(.Count).Interior
        .Color = 5296274
    End With
    Add Type:=xlExpression, Operator:=xlLess, _
        Formula1:="=50"
    With .Item(.Count).Interior
        .Color = 15773696
    End With
End With
End Sub

推荐答案

一个简单的宏录制给出：

A simple macro recording gives:

Sub Macro1()
    Range("B1:B10").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="50", Formula2:="90"
    Selection.FormatConditions(1).Interior.ColorIndex = 7
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="90"
    Selection.FormatConditions(2).Interior.ColorIndex = 4
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="50"
    Selection.FormatConditions(3).Interior.ColorIndex = 41
End Sub

使用with 1行的语法是无用的，因为它的用法是在重复的行上保存一些键入

Using the with syntax for 1 line is useless since its usage is to save some typing on repeated lines

With .Item(.Count).Interior
    .Color = 49407
End With

可替换为

can be replaces with

.Item(.Count).Interior.Color = 49407

为什么你使用选择，因为你没有设置它？



您应该使用语法修改有关的知识。

Why do you use Selection since you did not set it ?

You should revise your knowledge about with syntax.

这篇关于如何使用VBA代码的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！