excel实现鼠标用不同颜色十字定位表格 - 图文

excel实现鼠标用不同颜色定位表格

参考文档：http://jingyan.http://m.wodefanwen.com//article/375c8e198cf51525f3a22966.html 实现鼠标十字定位目标，效果见下图：

由于长期需要用excel进行数据录入，当excel数据一多，经常由于行和列的问题会看错。为了避免这种情况。就想到用用下面的办法解决这个问题

1.实现的效果就是鼠标点到那，都有一个不同的颜色区分出，鼠标所在位置的行和列

2.我用的版本是office 2010，打开excel，新建如下图。 3.在sheet1下标签处，点击鼠标右键，出现如下图 4.选择查看代码

5.看到如下界面，插入如下代码

Code1 (office2010版本可用，office2007未测试) Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) On Error Resume Next Cells.FormatConditions.Delete iColor =39 With Target.EntireRow.FormatConditions .Delete .Add xlExpression, , \ .Item(1).Interior.ColorIndex = iColor End With With Target.EntireColumn.FormatConditions .Delete .Add xlExpression, , \ .Item(1).Interior.ColorIndex = iColor End With End Sub

注：iColor = 34（绿色） 38（粉色）6(黄色) iColor =39 紫色

Code2 (office2010版本可用，office2007未测试) 该代码可实现横竖是两种不同颜色,但是原表格底色变成白色 Private Sub Worksheet_SelectionChange(ByVal Target As Range) Rows.Interior.ColorIndex = 0 Rows(Target.Row).Interior.ColorIndex = 39 Columns(Target.Column).Interior.ColorIndex = 42 End Sub

Code3 (office2010版本不可用，office2007未测试) Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count> 1 Then Exit Sub If Target.Column>= 9 And Target.Column<= 48 Then With Target.Interior If .ColorIndex = 3 Then .ColorIndex = xlNone Else .ColorIndex = 3 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End If End With End If If Target.Column>= 50 And Target.Column<= 67 Then With Target.Interior If .ColorIndex = 5 Then .ColorIndex = xlNone Else .ColorIndex = 5 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End If End With End If End Sub

Code4 (office2010版本可用，office2007未测试) Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) On Error Resume Next Cells.FormatConditions.Delete iColor = Int(50 * Rnd() + 2) With Target.EntireRow.FormatConditions .Delete .Add xlExpression, , \.Item(1).Interior.ColorIndex = iColor End With With Target.EntireColumn.FormatConditions .Delete .Add xlExpression, , \.Item(1).Interior.ColorIndex = iColor End With End Sub

Code5 (office2010版本可用，office2007未测试) Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) On Error Resume Next Cells.FormatConditions.Delete 'iColor = Int(50 * Rnd() + 2) iColor = 34 With Target.EntireRow.FormatConditions .Delete .Add xlExpression, , \ .Item(1).Interior.ColorIndex = iColor End With

本文来源：https://www.bwwdw.com/article/ik3d.html