文章/答案/技术大牛

发布

社区首页 >问答首页 >如何通过vba代码获得条件格式？

问如何通过vba代码获得条件格式？
EN

Stack Overflow用户

提问于 2022-03-09 15:09:23

回答 1查看 80关注 0票数 -2

下午好,

我现在有这张桌子。

子表()

Range("A2:D2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Font.Bold = True
Range("A2").Select
ActiveCell.FormulaR1C1 = "Name"
Range("B2").Select
ActiveCell.FormulaR1C1 = "Birthday"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Years old"
Range("D2").Select
ActiveCell.FormulaR1C1 = "Presentation date"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Mary"
Range("B3").Select
ActiveCell.FormulaR1C1 = "5/2/2004"
Range("C3").Select
ActiveCell.FormulaR1C1 = "17"
Range("D3").Select
ActiveCell.FormulaR1C1 = "2/9/2022"
Range("D4").Select
ActiveCell.FormulaR1C1 = "12/16/2022"
Range("D5").Select
ActiveCell.FormulaR1C1 = "8/15/2022"
Range("D6").Select
ActiveCell.FormulaR1C1 = "6/5/2022"
Range("D7").Select
ActiveCell.FormulaR1C1 = "1/5/2022"
Range("D8").Select
ActiveCell.FormulaR1C1 = "6/5/2022"
Range("D9").Select
ActiveCell.FormulaR1C1 = "12/6/2022"
Range("D10").Select
ActiveCell.FormulaR1C1 = "12/19/2022"
Range("D11").Select
ActiveCell.FormulaR1C1 = "2/14/2022"
Range("C6").Select
ActiveCell.FormulaR1C1 = "39"
Range("C7").Select
ActiveCell.FormulaR1C1 = "26"
Range("C9").Select
ActiveCell.FormulaR1C1 = "20"
Range("C10").Select
ActiveCell.FormulaR1C1 = "48"
Range("C11").Select
ActiveCell.FormulaR1C1 = "76"
Range("A6").Select
ActiveCell.FormulaR1C1 = "John"
Range("B6").Select
ActiveCell.FormulaR1C1 = "4/3/1982"
Range("B7").Select
ActiveCell.FormulaR1C1 = "12/7/1995"
Range("B9").Select
ActiveCell.FormulaR1C1 = "5/1/2001"
Range("B10").Select
ActiveCell.FormulaR1C1 = "11/15/1973"
Range("B11").Select
ActiveCell.FormulaR1C1 = "10/19/1945"
Range("A11").Select
ActiveCell.FormulaR1C1 = "Bia"
Range("A10").Select
ActiveCell.FormulaR1C1 = "Greny"
Range("A9").Select
ActiveCell.FormulaR1C1 = "Amy"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Louis"
Range("A5:D5,A6:D6,A8:D8,A9:D9,A10:D10,A11:D11").Select
Range("A11").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

结束子对象

有谁知道根据个人年龄有条件地格式化行(水平绘制表格单元格)的代码吗？

其中：

子条件()

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "<18"
Range("G2").Select
With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
ActiveCell.FormulaR1C1 = "grey"
Range("F2").Select
ActiveCell.FormulaR1C1 = "<18"
Range("G2").Select
ActiveCell.FormulaR1C1 = ""
Range("F2").Select
ActiveCell.FormulaR1C1 = ""
Range("F2").Select
ActiveCell.FormulaR1C1 = "<=18"
Range("G2").Select
ActiveCell.FormulaR1C1 = "grey"
Range("F3").Select
ActiveCell.FormulaR1C1 = "19-25"
Range("G3").Select
ActiveCell.FormulaR1C1 = "yellow"
Range("F4").Select
ActiveCell.FormulaR1C1 = "26-40"
Range("G4").Select
ActiveCell.FormulaR1C1 = "dark yellow"
Range("F5").Select
ActiveCell.FormulaR1C1 = "41-60"
Range("G5").Select
ActiveCell.FormulaR1C1 = "orange"
Range("F6").Select
ActiveCell.FormulaR1C1 = ">=61"
Range("G6").Select
ActiveCell.FormulaR1C1 = "green"
Range("H4").Select
Columns("G:G").EntireColumn.AutoFit
Range("F2:G6").Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("H11").Select

结束子对象

同时按升序对演示日期进行排序(但按名称进行分组，即按组排序)？

谢谢

vba

formatting

format

excel

Stack Overflow用户

发布于 2022-03-09 15:30:50

只需将5条不同的规则添加到所需的范围

With Range("A:A")
    ' <= 18
    .FormatConditions.Add(Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=18").Interior.Color = 10000
    
    ' 19 … 25
    .FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=19", Formula2:="=25").Interior.Color = 20000
    
    ' 26 … 40
    .FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=26", Formula2:="=40").Interior.Color = 65535
    
    ' 41 … 59
    .FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=41", Formula2:="=59").Interior.Color = 49407

    ' >= 60
    .FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=60").Interior.Color = 13311
End With

票数 2

查看全部 1 条回答

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/71411559

复制

相似问题

问如何通过vba代码获得条件格式？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何通过vba代码获得条件格式？EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何通过vba代码获得条件格式？
EN