来自记录宏的条件格式代码导致错误

问题描述：

从记录宏到条件格式列生成的代码不起作用。下面星号中的代码是提示错误的代码。来自记录宏的条件格式代码导致错误

Worksheets("Characterisation").Select 
    Columns("D:D").Select 
    **Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _ 
    xlConditionValueLowestValue** 
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor 
    .Color = 7039480 
    .TintAndShade = 0 
End With 
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _ 
    xlConditionValuePercentile 
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50 
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor 
    .Color = 8711167 
    .TintAndShade = 0 
End With 
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _ 
    xlConditionValueHighestValue 
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor 
    .Color = 8109667 
    .TintAndShade = 0 
End With

任何帮助将不胜感激，提前致谢！

什么错误没有给出 –

如果'Selection.FormatConditions（1）'抛出一个错误，那么你应该检查选择是否包含格式条件alr伊迪。如果没有，那么你必须先添加一个。顺便说一句：你应该提到**哪个**错误被抛出。你应该避免使用'Select'并使用'Selection'。您应该使用对象变量。 –

我的代码的其余部分是用于将行移动到另一个表格，这会中断格式化，因此在那里移动的行没有初始格式，这会是一个问题吗？错误是“运行时错误'438'：对象不支持此属性或方法” – Tom

答

我尝试下面的代码，它运行没有我在我的Excel工作表进行的测试误差：

Option Explicit 

Sub CondFormatting() 

With Worksheets("Characterisation") 
    ' add the new type of Conditional Formatting 
    .Columns("D:D").FormatConditions.AddColorScale ColorScaleType:=3 

    With .Columns("D:D")       
     With .FormatConditions(1).ColorScaleCriteria(1) 
      .Type = xlConditionValueLowestValue 
      .FormatColor.Color = 7039480 
      .FormatColor.TintAndShade = 0 
     End With 

     With .FormatConditions(1).ColorScaleCriteria(2) 
      .Type = xlConditionValuePercentile 
      .Value = 50 
      .FormatColor.Color = 8711167 
      .FormatColor.TintAndShade = 0 
     End With 

     With .FormatConditions(1).ColorScaleCriteria(3) 
      .Type = xlConditionValueHighestValue 
      .FormatColor.Color = 8109667 
      .FormatColor.TintAndShade = 0 
     End With   
    End With 

End With 

End Sub

这仍然会导致与'With .FormatConditions（1）.ColorScaleCriteria（1）'行一样的错误。我想我的代码的其余部分有问题，或者因为我已经需要在重新应用它的列中不需要格式化。 – Tom

@Tom你可以发布你的代码的其余部分（相关部分），也许你正在改变导致这个错误的东西？ –

@Tom尝试编辑的代码 - 添加了条件格式类型'.AddColorScale ColorScaleType：= 3'的创建 –

答

这不是一个答案。

@Shai我一直在玩耍，尝试获得更好的结果，到目前为止，我的格式和过滤代码如下所示，过滤器应该将'VIP'行带到顶部，然后是1,2， 3 ...

With Worksheets("Burn") 
    Columns("D:D").Select 
Selection.FormatConditions.Delete 
Selection.FormatConditions.AddColorScale ColorScaleType:=3 
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _ 
    xlConditionValueLowestValue 
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor 
    .Color = 7039480 
    .TintAndShade = 0 
End With 
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _ 
    xlConditionValuePercentile 
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50 
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor 
    .Color = 8711167 
    .TintAndShade = 0 

End With 
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _ 
    xlConditionValueHighestValue 
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor 
    .Color = 8109667 
    .TintAndShade = 0 
End With 
Selection.FormatConditions.Add Type:=xlTextString, String:="VIP", _ 
    TextOperator:=xlContains 
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
With Selection.FormatConditions(1).Font 
    .Bold = True 
    .Italic = False 
    .Color = -16711681 
    .TintAndShade = 0 
End With 
With Selection.FormatConditions(1).Interior 
    .PatternColorIndex = xlAutomatic 
    .ThemeColor = xlThemeColorLight1 
    .TintAndShade = 0 
End With 
Selection.FormatConditions(1).StopIfTrue = False 

ActiveWorkbook.Worksheets("Burn").AutoFilter.Sort.SortFields.Clear 
    ActiveWorkbook.Worksheets("Burn").AutoFilter.Sort.SortFields.Add _ 
    Key:=Range("D14"), SortOn:=xlSortOnValues, Order:=xlAscending, _ 
    DataOption:=xlSortNormal 
    With ActiveWorkbook.Worksheets("Burn").AutoFilter.Sort 
    .Header = xlYes 
    .MatchCase = False 
    .Orientation = xlTopToBottom 
    .SortMethod = xlPinYin 
    .Apply 
    End With 
    ActiveWorkbook.Worksheets("Burn").AutoFilter.Sort.SortFields.Clear 
    ActiveWorkbook.Worksheets("Burn").AutoFilter.Sort.SortFields.Add(_ 
    Range("D14"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _ 
    Color = RGB(0, 0, 0) 
    With ActiveWorkbook.Worksheets("Burn").AutoFilter.Sort 
    .Header = xlYes 
    .MatchCase = False 
    .Orientation = xlTopToBottom 
    .SortMethod = xlPinYin 
    .Apply 
End With 

End With

这是我使用的，除非有“VIP”或空白分配以创建新的行高值码：

If IsNumeric("D15") Then 
    MaxVal1 = Application.WorksheetFunction.Max(wk2.Range("D15:D1000")) 
    Range("D15").Value = MaxVal1 + 1 

    End If

来自记录宏的条件格式代码导致错误

相关推荐