资源描述
excel实现鼠标用不同颜色定位表格 参考文档：实现鼠标十字定位目标，效果见下图：由于长期需要用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, , TRUE .Item(1).Interior.ColorIndex = iColor End With With Target.EntireColumn.FormatConditions .Delete .Add xlExpression, , TRUE .Item(1).Interior.ColorIndex = iColor End WithEnd 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 = 39Columns(Target.Column).Interior.ColorIndex = 42End SubCode3 (office2010版本不可用，office2007未测试)Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 9 And 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 IfEnd SubCode4 (office2010版本可用，office2007未测试)Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)On Error Resume NextCells.FormatConditions.DeleteiColor = Int(50 * Rnd() + 2)With Target.EntireRow.FormatConditions.Delete.Add xlExpression, , TRUE.Item(1).Interior.ColorIndex = iColorEnd WithWith Target.EntireColumn.FormatConditions.Delete.Add xlExpression, , TRUE.Item(1).Interior.ColorIndex = iColorEnd WithEnd SubCode5 (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, , TRUE .Item(1).Interior.ColorIndex = iColor End With With Target.EntireColumn.FormatConditions .Delete .Add xlExpression, , TRUE .Item(1).Interior.ColorIndex = iColor End With If Target.Row = 3 Then If Sheet1.Cells(3, Target.Column) Then For i = 1 To 450 If Sheet1.Cells(i, 3).Value = Sheet1.Cells(3, Target.Column).Value Then Sheet1.Range(c & i).Select End If Next i End If End IfEnd SubCode6 代码解释#Region EXCEL背景色设定 EXCEL背景色设定 开始行号码 结束行号码 开始列号码 结束列号码 ColorIndex ColorIndex请用Excel宏确认一下 Public Function XlsSetBackColor(ByVal iRowS As Integer, ByVal iRowE As Integer, _ ByVal iColS As Integer, ByVal iColE As Integer, _ ByVal iColorIdx As Integer) As XLS_RESULT Try 行号码、列号码的开始位置和结束位置一样时 If iRowS = iRowE AndAlso _ iColS = iColE Then Range取得 oRange = oSheet.Range(Me.GetXlsRange(iColS) & CStr(iRowS) Else Range取得 oRange = oSheet.Range(Me.GetXlsRange(iColS) & CStr(iRowS), Me.GetXlsRange(iColE) & CStr(iRowE) End If oInterior = oRange.Interior oInterior.ColorIndex = iColorIdx MRComObject(oInterior) MRComObject(oRange) Return XLS_RESULT.XLS_OK Catch ex As Exception Me.Xls() Return XLS_RESULT.XLS_NG Finally MRComObject(oInterior) MRComObject(oRange) End Try End Function #End Region复制粘贴如下代码：ctrl+S 保存6.保存，可以看到，如下效果了，当鼠标点到那，很明显就能把行和列这样区分出来了。文档可自由编辑打印
展开阅读全文