Forum Discussion
Excel highlight numbers in range by input
Sorry, I didn't realize you had this on multiple worksheets in the workbook, changed to a workbook change event. So the code goes in ThisWorkbook. Added to what you had posted and attached.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim TicketRange As Range
Dim DrawRange As Range
Dim Cell As Range
Dim CheckRange As Range
Set TicketRange = Range("B4:F23")
Set DrawRange = Range("H4:L23")
For Each Cell In TicketRange
Set CheckRange = Range("H4:L23").Find(What:=Cell.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not CheckRange Is Nothing And Cell.Value <> "" Then
Cell.Interior.ColorIndex = 4
Else
Cell.Interior.ColorIndex = 0
End If
Next Cell
End Sub
For me is working only that Macro which i need to run manually.
Sorry, I didn't realize you had this on multiple worksheets in the workbook, changed to a workbook change event. So the code goes in ThisWorkbook. Added to what you had posted and attached.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim TicketRange As Range
Dim DrawRange As Range
Dim Cell As Range
Dim CheckRange As Range
Set TicketRange = Range("B4:F23")
Set DrawRange = Range("H4:L23")
For Each Cell In TicketRange
Set CheckRange = Range("H4:L23").Find(What:=Cell.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not CheckRange Is Nothing And Cell.Value <> "" Then
Cell.Interior.ColorIndex = 4
Else
Cell.Interior.ColorIndex = 0
End If
Next Cell
End Sub
Oh thanks :)
It's working :)
I'll learn from it, I promise :)
But is it possible to make that the count of coloured numbers shows automatically when it's matching number in lottery range from draw range?
Because when i use Your VBA code, it's working good. But when i enter in draw range, i need to P7 - P11 manually and hit enter.You can force recalculation of the workbook (Ie. your function which is not auto recalculating) by adding this line to the end of my sub:
Application.Calculate
I can maybe come up with something a bit more elegant but I've got to take off now.
Thanks :)
You've helped me a lot :)
Happy Holidays :)