Can anyone help me with a solution to this problem?
Still have been trying to find a way to do this but no luck. I tried using vlookup to look up the expiration date of the inputed certificate number but the problem is that I don't know how to tie in ANY inputed value to vlookup since vlookup needs a specific value to look for, i.e.
=vlookup(VALUE, table_array, index_number, not_exact_match )
I tried entering the same cell into the value slot but then it said that it was a circular reference.
My next thought was that I could use if and then statements to change the background color based on what was entered into a cell by a user and check the expiration date which was based on something like this
Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("R7:R1000")
For Each Cell In MyPlage
If Cell.Value = "Extreme" Then
Cell.Interior.ColorIndex = 3
End If
If Cell.Value = "Hight" Then
Cell.Interior.ColorIndex = 4
End If
If Cell.Value = "Medium" Then
Cell.Interior.ColorIndex = 18
End If
If Cell.Value = "Low" Then
Cell.Interior.ColorIndex = 6
End If
If Cell.Value <> "Extreme" And Cell.Value <> "Hight" And Cell.Value <> "Medium" And Cell.Value <> "Low" Then
Cell.Interior.ColorIndex = xlNone
End If
Next
End Sub
Except I wanted to substitute the If statements with something like this,
If vlookup(6(certificate number), K5:K150, 3,false) = 0
Then cell.interior.colorindex = 46
else cell.interior.colorindex = 43
End if
And have one like that for each certificate
I know the structure is incorrect but I think i'm on the right track.
Any help would be greatly appreciated!
Bookmarks