[excel] excel的一些方法记录

来源：互联网发布：kate moss知乎编辑：程序博客网时间：2024/05/21 08:42

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

excel定义单元格名称

1.定义

插入>名称>定义>备注/引用位置=收支表!$B$2:$B$999

2.使用

{=sum((month(时间)=$H$2)*(收支项目=B5)*收支金额)}

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

excel表格根据单元格内容设置填充颜色的方法

根据单元格内容设置指定行颜色的方法

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rg As Range

    If Target.Count > 1 Then
        Exit Sub
    End If

    If Target.Column = 15 And Target.Row > 7 Then
      ' 整行 Target.EntireRow.Interior.Color = RGB(0,250,0)

     With Range("B" & Target.Row, "Z" & Target.Row)
            Select Case Target
            Case "正常运行"
                .Interior.Color = RGB(0, 250, 0)
            Case "风险运行"
                .Interior.Color = RGB(180, 100, 0)
            Case "逾期运行"
                .Interior.Color = RGB(255, 255, 0)
            Case "正常关闭"
                .Interior.Color = RGB(100, 100, 100)
            Case "逾期关闭"
                .Interior.Color = RGB(100, 200, 100)
            Case "异常终止"
                .Interior.Color = RGB(100, 200, 250)
            Case "暂时挂起"
                .Interior.Color = RGB(186, 241, 186)
            Case "状态未知"
                .Interior.Color = RGB(255, 255, 100)
            End Select
        End With
    End If
End Sub