S
sharkman992000
hi all,
just 2 queries for performing cell number search within a spreadsheet
and highlighting background search and also performing a number count
of the amount of instances within a range.
Data is:
Number_1 Number_2 Number_3 Number_4 Number_5 Number_6 Supp_1 Supp_2
27 3 11 38 15 22 14 44
2 25 40 42 5 16 43 33
40 33 21 41 42 2 43 28
33 43 12 18 24 9 35 45
44 10 38 8 37 39 22 6
1) I have code as follows but am not able to work out how to execute it
automatically so that it will highlight the numbers in the cells for me
???
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("xx:xx")) is Nothing Then
Select Case Target
Case 3
icolor = 6
Case 11
icolor = 6
Case 38
icolor = 6
Case 15
icolor = 6
Case 22
icolor = 6
Case 14
icolor = 6
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
2) Also would like to work out how many times the numbers appear eg how
many 3's etc to determine frequency
thanks in advance,
sharky
just 2 queries for performing cell number search within a spreadsheet
and highlighting background search and also performing a number count
of the amount of instances within a range.
Data is:
Number_1 Number_2 Number_3 Number_4 Number_5 Number_6 Supp_1 Supp_2
27 3 11 38 15 22 14 44
2 25 40 42 5 16 43 33
40 33 21 41 42 2 43 28
33 43 12 18 24 9 35 45
44 10 38 8 37 39 22 6
1) I have code as follows but am not able to work out how to execute it
automatically so that it will highlight the numbers in the cells for me
???
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("xx:xx")) is Nothing Then
Select Case Target
Case 3
icolor = 6
Case 11
icolor = 6
Case 38
icolor = 6
Case 15
icolor = 6
Case 22
icolor = 6
Case 14
icolor = 6
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
2) Also would like to work out how many times the numbers appear eg how
many 3's etc to determine frequency
thanks in advance,
sharky