VBA code to search by meeting a criteria

[awale]

(completely new to excel VBA )

Hello

I am having problems with a VBA Excel 2010 program code.

I am trying to read data from a spreadsheet on excel 2010. what I have is a set of data (see below) and I am trying to write a code that will let me use a msg box and ask me to write down the name I am looking for e.g. "Name 1" from the list of names in the column and then I want to set a criteria where if the number in the columns are equal to zero and again for a different column = 0 ("name 5"), then highlight red any number in column "Name 8 and Name 9" that is greater than let say 30 (just a random example). the important thing is, the red highlight of column "Name 8/9" must only occur if the numbers is row "Name 1" and "Name 5" are equal to zero.

I have already done this but I only used the column numbers e.g. A1:A5. now I need to use the name of the column because I want to use the code for a different excel spreadsheets but the names of columns are in different positions for each excel sheet, but if I use the names, no matter which column along excel they are I will always find the right column I am looking for and set the criteria.

the criteria for "Name 1/5" will always be = 0 or =1 but the program has to ask me to choose that when I search for it.

if you look below at the example, you can see the red highlight are when criteria of =0 is met for Name 1 and Name 5 and the number in Name8/9 are greater than 30. when it is not greater than 30 and it still meets the criteria it is highlighted blue in the excel spreadsheet example. ALL OTHER NAMES MUST BE IGNORED.

SEE EXAMPLE BELOW hopefully this will make sense. “hopefully"

Name 1	Name 2	Name 3	Name 4	Name 5	Name 6	Name 7	Name 8	Name 9	Name 10
0	0	1	0	0	1	58	35	14	19
0	0	0	0	0	1	41	45	68	74
1	0	1	0	1	0	23	18	98	87
0	0	1	0	0	1	65	36	52	89
0	0	0	0	1	1	24	95	47	75
1	1	1	0	1	0	58	87	59	14
0	1	0	0	0	0	74	41	84	32
1	1	0	0	1	0	96	25	74	96
0	0	0	0	0	0	87	35	15	53
0	0	1	0	0	1	57	49	48	47
1	0	1	0	1	1	63	84	23	65
0	1	0	0	0	0	21	54	69	12
0	0	1	0	0	0	54	23	54	54
1	1	0	0	1	1	88	34	77	88
0	0	1	0	0	0	78	48	68	69
1	0	1	0	0	1	96	87	14	65
1	0	0	0	1	0	21	96	54	25
0	1	0	0	0	0	54	72	78	29
0	1	1	0	0	1	62	38	22	78
0	0	0	0	0	0	21	49	65	54
1	0	1	0	1	1	17	65	98	99
0	0	0	0	0	0	59	15	56	70
0	1	1	0	0	0	36	12	29	54
1	0	0	0	1	0	29	49	55	54 THANKS ALOT

<tbody>
</tbody>

 

[Arjan77]

.

 

[Arjan77]

Just to get you started, if you have a userform with a textbox and a commandbutton use this code:

Private Sub CommandButton1_Click()
Dim Ws1 As Worksheet

Set Ws1 = Worksheets(1)

For i = 1 To Ws1.Cells(Columns.Count, 5).End(xlToRight).Column
If TextBox1.Value = Ws1.Cells(1, i) Then
MsgBox i
GoTo Finish:
End If
Next

Finish:
End Sub

This will get you the number of the column with the name you typed in the textbox.

From there you will have to extend your code to perform the formatting of the cells, if you never used VBA this requires a lot of time.

 

[awale]

THIS IS WHAT I HAVE SO FAR

Private Sub CommandButton21_Click()

searchstring = InputBox("Input name?")

Set coll = Rows(1).Find(What:=searchstring, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If coll Is Nothing Then
    MsgBox "Name not found"
    Exit Sub
Else
    coll = coll.Column
    Lrow = Range(Cells(2, coll), Cells(2, coll)).CurrentRegion.Rows.Count
End If

    Cells.Interior.ColorIndex = 0
    For Each rw In Range(Cells(2, coll), Cells(Lrow, coll))

        If Application.Sum(rw.Resize(, 4)) = 0 Then
            rw.Interior.ColorIndex = 3
            For Each cll In rw.offset(, 4).Resize(, 18).Cells
                If cll.Value > 50 Then cll.Interior.ColorIndex = 3
            Next cll
        End If
    Next rw
End Sub

I want to be able to search for three three diffeent name and set the criteria( a message box that asks me whether I want it to be 1 or 0 ). so search name 1 ask me for criteria , 1 or 0 then name 2 then name 3.

 

[Arjan77]

Why don't you create a userform, you can enter all criteria at the same time. I would use a combobox so you can just pick the appropriate name.

Add a combobox to the userform and use this code:

Private Sub UserForm_Initialize()

For i = 1 To ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
ComboBox1.AddItem ActiveSheet.Cells(1, i).Value
Next

ComboBox1.ListIndex = 0

End Sub

The combobox will list all your column names.
Your column number will equal combobox1.listindex +1

Add two more comboboxes initialize them the same way, after that you use a commandbutton to perform actions for all three comboboxes.