☰

条件格式错误

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 microsoft-excel-2007 vba
条件格式错误

我无法使用下面的代码来使用条件格式，我不知道我在这里错了：

Sub condition_formatting_ms_sheet_test()

Application.Workbooks(file_name).Worksheets("ms").Activate

Application.Workbooks(file_name).Worksheets("ms").Range("a1:CZ1000").FormatConditions.Delete

Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=2"
Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=3"
Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=4"
Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=5"
Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=6"
Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=7"

Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions(1).Interior.Color = RGB(255, 0, 0)
Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions(2).Interior.Color = RGB(0, 255, 0)
Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions(3).Interior.Color = RGB(0, 200, 200)
Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions(4).Interior.Color = RGB(200, 200, 0)
Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions(5).Interior.Color = RGB(0, 100, 100)
Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").FormatConditions(6).Interior.Color = RGB(100, 0, 100)

Application.Workbooks(file_name).Worksheets("ms").Range("AG:AG").Font.Bold = True


End Sub

答案1

这对我来说可以。

Option Explicit

Sub condition_formatting_ms_sheet_test()



ThisWorkbook.Worksheets("Sheet1").Range("A:Z").FormatConditions.Delete

ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=2"
ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=3"
ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=4"
ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=5"
ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=6"
ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=7"

ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions(1).Interior.Color = RGB(255, 0, 0)
ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions(2).Interior.Color = RGB(0, 255, 0)
ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions(3).Interior.Color = RGB(0, 200, 200)
ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions(4).Interior.Color = RGB(200, 200, 0)
ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions(5).Interior.Color = RGB(0, 100, 100)
ThisWorkbook.Worksheets("Sheet1").Range("A:A").FormatConditions(6).Interior.Color = RGB(100, 0, 100)

ThisWorkbook.Worksheets("Sheet1").Range("A:A").Font.Bold = True


End Sub

也许你应该这样做 -

Option Explicit

Sub condition_formatting_ms_sheet_test()
Dim DesiredWB As Workbook
Set DesiredWB = Workbooks.Open(Filename:=file_name)



DesiredWB.Worksheets("Sheet1").Range("A:Z").FormatConditions.Delete

DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=2"
DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=3"
DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=4"
DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=5"
DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=6"
DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=7"

DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions(1).Interior.Color = RGB(255, 0, 0)
DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions(2).Interior.Color = RGB(0, 255, 0)
DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions(3).Interior.Color = RGB(0, 200, 200)
DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions(4).Interior.Color = RGB(200, 200, 0)
DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions(5).Interior.Color = RGB(0, 100, 100)
DesiredWB.Worksheets("Sheet1").Range("A:A").FormatConditions(6).Interior.Color = RGB(100, 0, 100)

DesiredWB.Worksheets("Sheet1").Range("A:A").Font.Bold = True


End Sub

相关内容