excel - VBA根据用户当前选择搜索Excel电子表格并突出显示结果

Question

我在工作簿的第一个表中有一列数据，我希望在工作簿的不同表中找到列中每个值的每个其他实例。然后我想突出显示结果。我在网上找到了一些东西，可以让用户提示他们正在寻找的东西，然后突出显示如下所示：

Sub HighlightSpecificValue()

'PURPOSE: Highlight all cells containing a specified values
'SOURCE: www.TheSpreadsheetGuru.com

Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range

'What value do you want to find?
  fnd = InputBox("I want to hightlight cells containing...", "Highlight")

'End Macro if Cancel Button is Clicked or no Text is Entered
  If fnd = vbNullString Then Exit Sub

    Set myRange = ActiveSheet.UsedRange
    Set LastCell = myRange.Cells(myRange.Cells.Count)
    Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)

    'Test to see if anything was found
      If Not FoundCell Is Nothing Then
        FirstFound = FoundCell.Address
      Else
    GoTo NothingFound
  End If

Set rng = FoundCell

'Loop until cycled through all unique finds
  Do Until FoundCell Is Nothing
    'Find next cell with fnd value
      Set FoundCell = myRange.FindNext(after:=FoundCell)

    'Add found cell to rng range variable
      Set rng = Union(rng, FoundCell)
  
    'Test to see if cycled through to first found cell
      If FoundCell.Address = FirstFound Then Exit Do
  
  Loop

'Highlight Found cells yellow
  rng.Interior.Color = RGB(255, 255, 0)

'Report Out Message
  MsgBox rng.Cells.Count & " cell(s) were found containing: " & fnd

Exit Sub

'Error Handler
NothingFound:
  MsgBox "No cells containing: " & fnd & " were found in this worksheet"

End Sub

可以将输入作为选择来代替输入框吗？

标签： excelvba