I am a novice user of VBA and new to excel vba. I am trying to run a range through a loop, and format the active location based on a vlookup. The code is listed below works with the exception of the 2 lines that are supposed to format the cell that was read ( V.Address.Interior.ColorIndex = 37). I believe i am useing the .Address wrong. How do i tell this format line to format the range that is active in the for loop. I have searched quite a bit online but must be using the wrong words to find the answer.
Thank you so much for the help
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim V As Variant
Dim ArrIn As Variant
Dim OH_Num As String
Dim first As Double
Dim second As Double
Dim missing As String
ArrIn = Range("A1:AB58")
For Each V In ArrIn
If WorksheetFunction.IsNumber(V) And V > 99 Then
On Error Resume Next 'Test for overhual type and set oh_type based on lookup
OH_Type = WorksheetFunction.VLookup(V, Worksheets("OH_Type").Range("A1:B500"), 2, False)
If Err.Number <> 0 Then
OH_Type = ""
End If
Select Case OH_Type 'Check/Count type and set cell color
Case "1st"
first = first + 1
V.Address.Interior.ColorIndex = 37
Case "2nd"
second = second + 1
V.Address.Interior.ColorIndex = 50
Case Else
missing = CStr(V) & ", " & missing
End Select
End If
Next
Worksheets("Current").Range("AK5") = first
Worksheets("Current").Range("AL5") = second
Worksheets("Current").Range("AK9") = missing
End Sub
Bookmarks