CODEWITHSUNDEEP
excelvba
Sally
Sally

Reputation: 3

How to highlight cells in an arbitary selection which match to a value in an array?

So for example, say my array is [32,43,92,99] ("hard coded" into the VBA code, don't need to worry about any inputs) and then in an excel sheet I highlight some rectangular grid of cells numbered 1-100. Would it be possible to cycle through the array and highlight the corresponding cells with those values in the sheet? In this case highlighting the cells reading 32, 43, 92, 99.

Sub readClipboard()
  Dim DataObj As New MSForms.DataObject
  Dim S As String
  DataObj.GetFromClipboard
  S = DataObj.GetText
  S = Application.Clean(S)
  S = Application.Trim(S)
  S = Split(S)
End Sub

So I'd want to then match up this array S with a selection, then highlight the cells which have values that match to values in the array.

Upvotes: 0

Views: 178

Answers (1)

JNevill
JNevill

Reputation: 50200

Here's one way to accomplish this. This is assuming you've selected/highlighted the range that has the values you are going to match. This just sets the color of the cell to an ugly gray. 

Sub highlightfromarray()
    S = Array(1, 5, 10, 23, 38)

    'Capture user range selection to a variable
    Dim userRange As Range
    Set userRange = Selection

    'VAriable for found range
    Dim foundRange As Range

    'Loop through array
    For Each numItem In S
        'Using Range.Find() method, find each item and highlight
        Set foundRange = userRange.Find(numItem)
        If Not foundRange Is Nothing Then foundRange.Interior.ColorIndex = 15
    Next
End Sub

Upvotes: 1

Related Questions