Button to color sertain numbers (Excel)

Alstrom - Jan 6, 2011 at 09:41 PM
 Alstrom - Jan 7, 2011 at 12:22 PM
Im looking for a way to make a button color all numers equal to the the number on the button..

as an eksample, lets say i have a row of buttons from 1 to 10,
and in the same sheet cells with numbers from 1 to 10 in no order.
I now want quickly to be able indentify all 3's and 7's by color.

"simple: click button 3, action: coloer all cells with the number 3 in it and let it be like that until i press the button again..

Im sorry for mt english, and hope you can get enough information from my jibber to compile a answer.. Pls hlp.



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
I shall give another solution print these instructions and follow(provided it suits you)


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 

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 
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
Thank you very much for you answer, i was of much help..