《excel实现鼠标用不同颜色十字定位表格》由会员分享，可在线阅读，更多相关《excel实现鼠标用不同颜色十字定位表格（8页珍藏版）》请在装配图网上搜索。
1、excel实现鼠标用不同颜色定位表格
参考文档：
实现鼠标十字定位目标，效果见下图：
由于长期需要用excel进行数据录入，当excel数据一多，经常由于行和列的问题会看错。为了避免这种情况。就想到用用下面的办法解决这个问题
1.实现的效果就是鼠标点到那，都有一个不同的颜色区分出，鼠标所在位置的行和列
2.我用的版本是office 2010，打开excel，新建如下图。
3.在sheet1下标签处，点击鼠标右键，出现如下图
4.选择查看代码
5.看到如下界面，插入如下代码
Code1 (office2010版本可用，office2007
2、未测试)
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.
3、FormatConditions
.Delete
.Add xlExpression, , "TRUE"
.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)
4、
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
5、 >= 9 And Target.Column <= 48 Then
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = xlNone
Else
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End If
6、 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
.Patter
7、nColorIndex = 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.EntireR
8、ow.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
End Sub
Code5 (office2010版本可用，office2007未测试)
Private S
9、ub 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.Color
10、Index = 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
11、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 If
End Sub
Code6 代码解释
#Region "EXCEL背景色设定"
EXCEL背景色设定
开始行号码
12、 结束行号码
开始列号码
结束列号码
ColorIndex
ColorIndex请用Excel宏确认一下
Public Function XlsSetBackColor(ByVal iRowS As In
13、teger, ByVal iRowE As Integer, _
ByVal iColS As Integer, ByVal iColE As Integer, _
ByVal iColorIdx As Integer) As XLS_RESULT
Try
行号码、列号码的开始位置和结束位置一样时
If iRowS = iRowE AndAlso _
14、 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))
15、 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.保存，可以看到，如下效果了，当鼠标点到那，很明显就能把行和列这样区分出来了。
文档可自由编辑打印
excel实现鼠标用不同颜色十字定位表格
