Ошибка форматирования условия

Question

Я не могу использовать форматирование условий с помощью приведенного ниже кода, я не знаю, где я ошибаюсь:

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

Answer 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