《excel實(shí)現(xiàn)鼠標(biāo)用不同顏色十字定位表格》由會(huì)員分享，可在線閱讀，更多相關(guān)《excel實(shí)現(xiàn)鼠標(biāo)用不同顏色十字定位表格（8頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。
1、excel實(shí)現(xiàn)鼠標(biāo)用不同顏色定位表格
參考文檔：
實(shí)現(xiàn)鼠標(biāo)十字定位目標(biāo)，效果見(jiàn)下圖：
由于長(zhǎng)期需要用excel進(jìn)行數(shù)據(jù)錄入，當(dāng)excel數(shù)據(jù)一多，經(jīng)常由于行和列的問(wèn)題會(huì)看錯(cuò)。為了避免這種情況。就想到用用下面的辦法解決這個(gè)問(wèn)題
1.實(shí)現(xiàn)的效果就是鼠標(biāo)點(diǎn)到那，都有一個(gè)不同的顏色區(qū)分出，鼠標(biāo)所在位置的行和列
2.我用的版本是office 2010，打開(kāi)excel，新建如下圖。
3.在sheet1下標(biāo)簽處，點(diǎn)擊鼠標(biāo)右鍵，出現(xiàn)如下圖
4.選擇查看代碼
5.看到如下界面，插入如下代碼
Code1 (office2010版本可用，office2007
2、未測(cè)試)
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未測(cè)試) 該代碼可實(shí)現(xiàn)橫豎是兩種不同顏色,但是原表格底色變成白色
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未測(cè)試)
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未測(cè)試)
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未測(cè)試)
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背景色設(shè)定"
EXCEL背景色設(shè)定
開(kāi)始行號(hào)碼
12、 結(jié)束行號(hào)碼
開(kāi)始列號(hào)碼
結(jié)束列號(hào)碼
ColorIndex
ColorIndex請(qǐng)用Excel宏確認(rèn)一下
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
行號(hào)碼、列號(hào)碼的開(kāi)始位置和結(jié)束位置一樣時(shí)
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
復(fù)制粘貼如下代碼：ctrl+S 保存
6.保存，可以看到，如下效果了，當(dāng)鼠標(biāo)點(diǎn)到那，很明顯就能把行和列這樣區(qū)分出來(lái)了。
文檔可自由編輯打印
excel實(shí)現(xiàn)鼠標(biāo)用不同顏色十字定位表格
