macro help

[Ron99]

Hi,

I need a message box, where i need to type in the numbers.

I have item codes in column A

123456
456789
147896
158496
123456

when I type in the number in message box for example " 123456" if the numbers are available in that column I need those numbers to be colored, so as and when I type the numbers if the numbers are available I need the numbers to get colored else a message saying number not found.


Regards,
Ron

 

[kevatarvind]

try below

Sub Test()
Dim my As Variant
my = Application.InputBox("Enter No")
my = Int(Val(my))
Set Rng = Range("A:A")
For Each cell In Rng
If cell = my Then
cell.Interior.ColorIndex = 6
Else
End If
Next
End Sub

 

[hiker95]

Ron99,

Sample raw data:

Excel 2007
	A
1	123456
2	456789
3	147896
4	158496
5	123456
6
Sheet1

If we search for a valid positive number, like, 123456, we get:

Excel 2007
	A
1	123456
2	456789
3	147896
4	158496
5	123456
6
Sheet1

If you were to enter anything but a positive number you would get an error message, and, the macro would terminate.

And, the macro will only test the used range in column A, not all cells in column A.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ColorMyNumber()
' hiker95, 04/10/2013
' http://www.mrexcel.com/forum/excel-questions/696141-macro-help.html
Dim a As Variant, my As Long, i As Long, n As Long
On Error Resume Next
my = Application.InputBox("Enter No")
On Error GoTo ErrorExit
If my <= 0 Then GoTo ErrorExit
a = Cells(1).CurrentRegion
For i = 1 To UBound(a, 1)
  If a(i, 1) = my Then
    Cells(i, 1).Interior.ColorIndex = 6
    n = n + 1
  End If
Next i
If n = 0 Then
  MsgBox "The macro did not find any numbers!"
Else
  MsgBox "The macro found " & n & " numbers."
End If
Exit Sub
ErrorExit:
MsgBox "You did not enter a valid positive number - macro terminated!"
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ColorMyNumber macro.

 

[Peter_SSs]

the macro will only test the used range in column A, not all cells in column A.

Actually, your code would need modification to be sure to meet that claim.

1. In this example, your code would only test 3 cells, not the used range in column A

Excel Workbook
	A	B	C
1	Nums
2	100077	Data
3	100024	Data
4	100062	Data
5
6	100006	Data
7	100049	Data
8	100078	Data
9	100057	Data
Colour Cells

2. In this example, assuming the rest of column A is blank and the rest of column B is populated, it will test the whole of column A, not just the 5 used cells in that column.

Excel Workbook
	A	B
1	Nums
2	100077	Data
3	100024	Data
4	100062	Data
5	100057	Data
6		Data
7		Data
8		Data
Colour Cells

 

[hiker95]

ADVERTISEMENT

Be right back.

 

[Peter_SSs]

What about just this simple change?

a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value

 

[hiker95]

Peter_SSs,

And, the macro will only test the used range in column A, not all cells in column A.

Nice catch - thanks. This time I did not ask Ron99 for a screenshot of the actual raw data.

Sample raw data:

Excel 2007
	A
1	123456
2	456789
3	147896
4	158496
5	123456
6
7	111111
8	123456
9	222222
10	123456
11
Sheet1

You would enter 123456 into the InputBox, and then you would get this:

Excel 2007
	A
1	123456
2	456789
3	147896
4	158496
5	123456
6
7	111111
8	123456
9	222222
10	123456
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ColorMyNumberV2()
' hiker95, 04/11/2013
' http://www.mrexcel.com/forum/excel-questions/696141-macro-help.html
Dim a As Variant, lr As Long, my As Long, i As Long, n As Long
On Error Resume Next
my = Application.InputBox("Enter No")
On Error GoTo ErrorExit
If my <= 0 Then GoTo ErrorExit
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A1:A" & lr)
For i = 1 To UBound(a, 1)
  If a(i, 1) = my Then
    Cells(i, 1).Interior.ColorIndex = 6
    n = n + 1
  End If
Next i
If n = 0 Then
  MsgBox "The macro did not find any numbers!"
Else
  MsgBox "The macro found " & n & " numbers."
End If
Exit Sub
ErrorExit:
MsgBox "You did not enter a valid positive number - macro terminated!"
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ColorMyNumberV2 macro.