NEW TO VBA NEED HELP PLEASE!! thank you :)

[awale]

Hello Everyone.

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

[SamT]

You posted this to the wrong folder. I moved it to the Excel Help Forum. I hope it is not too late

[jolivanes]

An example workbook might do wonders. (Code does not work very well on pictures)

[jolivanes]

Not sophisticated but very easy to understand.
Select Case would be better I think but for now it is bed time.
Maybe someone else is so gracious to change the code for you in the meantime.

Sub Long_Winded()
    Dim a As String, aa As String, b As String, bb As String
    Dim aRow As Long, aCol As Long, aaRow As Long, aaCol As Long
    Dim bRow As Long, bCol As Long, bbRow As Long, bbCol As Long
    Dim ValueColor As Long
    Dim lr As Long, j As Long


    a = Application.InputBox(Prompt:="Please enter the first name", Title:="Name required")
    aa = Application.InputBox(Prompt:="Please enter the second name", Title:="Name required")
    b = Application.InputBox(Prompt:="Please enter the first name to be colored", Title:="Name required")
    bb = Application.InputBox(Prompt:="Please enter the second name to be colored", Title:="Name required")
    ValueColor = Application.InputBox(Prompt:="Please enter value for color change", Title:="Value required")


    aRow = Cells.Find(a, , , 1).Row
    aCol = Cells.Find(a, , , 1).Column
    aaRow = Cells.Find(aa, , , 1).Row
    aaCol = Cells.Find(aa, , , 1).Column
    bRow = Cells.Find(b, , , 1).Row
    bCol = Cells.Find(b, , , 1).Column
    bbRow = Cells.Find(bb, , , 1).Row
    bbCol = Cells.Find(bb, , , 1).Column
    lr = Cells(Rows.Count, aCol).End(xlUp).Row


    For j = aRow + 1 To lr


        If Cells(j, aCol).Value = 0 And Cells(j, aaCol) = 0 Then
            If Cells(j, bCol).Value >= ValueColor Then Cells(j, bCol).Interior.Color = vbRed
            If Cells(j, bCol).Value < ValueColor Then Cells(j, bCol).Interior.Color = vbBlue
            If Cells(j, bbCol).Value >= ValueColor Then Cells(j, bbCol).Interior.Color = vbRed
            If Cells(j, bbCol).Value < ValueColor Then Cells(j, bbCol).Interior.Color = vbBlue
        End If


    Next j
End Sub