VBA单元格自适应高亮操作

1、单元格所在行和列高亮

第一种方式

Private Sub worksheet_selectionchange(ByVal target As Range)

    Cells.Interior.ColorIndex = xlColorIndexNone

    target.EntireRow.Interior.ColorIndex = 6

    target.EntireColumn.Interior.ColorIndex = 50

End Sub

第二种方式

Private Sub worksheet_selectionchange(ByVal target As Range)

    Dim rng As Range

    Cells.Interior.ColorIndex = xlNone

    Set rng = Application.Union(target.EntireRow, target.EntireColumn)

    rng.Interior.ColorIndex = 56

End Sub

需注意：代码需在sheet中运行，运行区域高亮后不可使用复制粘贴等操作，以前单元格格式会被新格式被覆盖。同时若代码中含单元格值改变时保护单元格也会和上面代码冲突。

2、展示所有颜色代码到单元格中

Sub 颜色()

    Dim i

    With Sheet2

        For i = 1 To 56

            .Cells(i, 1).Value = i

            .Cells(i, 1).Interior.ColorIndex = i

        Next

    End With

End Sub

3、数据录入后的单元格自动保护

Private Sub worksheet_selectionchange(ByVal target As Range)

    On Error Resume Next

    Sheet1.Unprotect Password:="hj1905"   '取消工作表单元格保护

    If target.Value <> "" Then

        target.Locked = True

        Sheet1.Protect Password:="hj1905"

    End If

End Sub

4、关于事件中的target

1）事件中可使用target的address属性限定target的单元格触发范围

2）target的row和column属性限定行列范围，整行和整列用entirerow 和entirecolumn

3) 用于intersect函数中，也可判定选定某区域执行某个操作

target在

[a1:a10], [c1:c10]范围会弹出相应地址

Private Sub worksheet_selectionchange(ByVal target As Range)

    If Not Application.Intersect(target, Union([a1:a10], [c1:c10])) Is Nothing Then

        MsgBox "you choose " & target.Address(0, 0)

    End If

End Sub

　保护单元格操作写法2：

Private Sub worksheet_selectionchange(ByVal target As Range)

    Dim rng As Range

    Set rng = Union([b2:c10], [e1:f10])

    If Application.Intersect(target, rng) Is Nothing Then Exit Sub

    [a1].Select

    MsgBox "you cannot edit " & rng.Address(0, 0) & " area"

End Sub