Button to color sertain numbers (Excel)
Solved/Closed
Related:
- Button to color sertain numbers (Excel)
- At button - Guide
- Convert numbers to words in excel - Guide
- Rj45 color - Guide
- Notepad++ change background color - Guide
- Excel date format dd.mm.yyyy - Guide
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 7, 2011 at 04:49 AM
Jan 7, 2011 at 04:49 AM
I shall give another solution print these instructions and follow(provided it suits you)
OPEN A NEW EXCEL FILE
now in vb editor copy this macro
click view menu-toolabars-form
in the list of form toolbars there is one called spinner
click it and draw a spinner button
right click the button and click "format control"
in that window choose control (the last item in the top row)
type 1 against "minimum value"
type 10 against "maximum value"
"incremental change" will be 1
cells link will be $F$1
click ok
again right click the spinner button and click "assign macro"
the name "test " will come up. highlight that and click ok
suppose the numbers are in column A
to test type some integers between1 and 10(some repeating twice or thrice, some numbers missing )from A1 to A20
save the file
now select any cell outside the spinner button and again click upward or downward arrow in the spinner button. see the value in F1 . if F1 is 1 nothing will happen if you click downward arrow similarly if F1 is 10 upward arrow will not give anything
otherwise go on clicking the downward or upward arrows, keep watch in F1 and see those numbers(whose value is F1 value) in column A will be marked red.
will this of use to you.
you can modify the plan
OPEN A NEW EXCEL FILE
now in vb editor copy this macro
Sub test()
Dim x As Integer, cfind() As Range, j As Integer, k As Integer, add As String
Columns("A:A").Interior.ColorIndex = xlNone
x = Range("F1").Value
j = WorksheetFunction.CountIf(Columns("A:A"), x)
If j = 0 Then
MsgBox "no such value is available in column A"
Exit Sub
End If
ReDim cfind(1 To j)
For k = 1 To j
Set cfind(k) = Columns("A:A").Cells.Find(what:=x, lookat:=xlWhole)
cfind(k).Interior.ColorIndex = 3
add = cfind(k).Address
Do
Set cfind(k) = Columns("A:A").Cells.FindNext(cfind(k))
If cfind(k) Is Nothing Then Exit Do
If cfind(k).Address = add Then Exit Do
cfind(k).Interior.ColorIndex = 3
Loop
Next k
End Sub
click view menu-toolabars-form
in the list of form toolbars there is one called spinner
click it and draw a spinner button
right click the button and click "format control"
in that window choose control (the last item in the top row)
type 1 against "minimum value"
type 10 against "maximum value"
"incremental change" will be 1
cells link will be $F$1
click ok
again right click the spinner button and click "assign macro"
the name "test " will come up. highlight that and click ok
suppose the numbers are in column A
to test type some integers between1 and 10(some repeating twice or thrice, some numbers missing )from A1 to A20
save the file
now select any cell outside the spinner button and again click upward or downward arrow in the spinner button. see the value in F1 . if F1 is 1 nothing will happen if you click downward arrow similarly if F1 is 10 upward arrow will not give anything
otherwise go on clicking the downward or upward arrows, keep watch in F1 and see those numbers(whose value is F1 value) in column A will be marked red.
will this of use to you.
you can modify the plan
Jan 7, 2011 at 12:22 PM
Alstrom