NEW TO VBA NEED HELP PLEASE!!

[awale]

Hello friends oftomorrow.

I have a problem with a simple excel spread sheet containg numbers that needsorting when they meet a criterias.
as you can see below I have this set of numbers and my question can anyone helpme write a program on VBA excel 2010
that will highlight any number that is over 50 when you get a row of 0's and ifany row has a 1 then leave any number over 50 blank(no highlight). example forthe two top rows they are both all zero's and in this instant, 96 will behighlighted (back ground) red (96>50).
the third row has a 1 even though the rest are zero's.

Below is two different code I have so far (VBA code) but it is not doing what Iwant, it just hight light any number over 50.
if you can

I have been at it for over a week and althougth this might be an hours job tosome, it is hard being a beginer.

0	0	0	0	12	39	24	96
0	0	0	0	65	87	44	87
1	0	0	0	41	45	65	35
1	1	1	0	25	42	65	63
0	0	0	1	74	99	41	35
1	0	1	0	36	12	47	14
1	1	0	0	21	35	95	18
0	0	0	0	54	69	32	74
1	0	0	0	98	43	65	96
1	0	0	0	31	31	14	35
0	0	0	0	25	52	25	62
0	1	1	0	41	54	14	68
1	0	0	0	23	45	47	18
0	1	1	0	47	21	26	49
0	0	0	0	12	74	35	42
0	1	1	1	36	69	50	15
0	0	0	0	14	25	48	27
0	0	0	0	78	97	78	28
1	0	1	1	25	29	24	75
0	1	1	1	41	73	56	15
0	0	0	0	65	11	41	72
0	0	0	0	44	9	39	81

Private Sub CommandButton21_Click()
Dim a, counterAs Integer
Dim cell As Range
For a = 1 To 22
If Cells(a,1).Value = 0 Then
For Each cell In Range("E1:H22")
If cell.Value > 50 Then
cell.Interior.ColorIndex = 3
Else
cell.Interior.ColorIndex = 0
End If
Next

counter = counter + 1
Cells(a, 1).Interior.ColorIndex = 3
Else
Cells(a, 1).Interior.ColorIndex = 0

End If
Next a

End Sub
'
'
'
'
'
'
'
Private SubCommandButton21_Click()
Dim cell, hellAs Range
'Dim i, j, count, counter As Integer

For Each cell In Range("E1:H22")
For Each hell In Range("A122")

If hell.Value = 0 Then
If cell.Value > 45 Then
cell.Interior.ColorIndex = 4
Else
cell.Interior.ColorIndex = 0
End If

hell.Interior.ColorIndex = 3
Else
hell.Interior.ColorIndex = 0

End If
Next
Next
End Sub

[p45cal]

If there can never be any negative numbers in the first 4 columns then this should suffice (it just adds the values in the first 4 columns together and tests for zero):

Sub blah()
For Each rw In Range("A1:H22").Rows
If Application.Sum(rw.Resize(, 4)) = 0 Then
    For Each cll In rw.Offset(, 4).Resize(, 4).Cells
      If cll.Value > 50 Then cll.Interior.ColorIndex = 3
    Next cll
  End If
Next rw
End Sub

If this is not the case then the cells in the first 4 columns needs to be tested for a zero in which case:

Sub blah2()
For Each rw In Range("A1:H22").Rows
  AllZeroes = True
  For Each cll In rw.Resize(, 4).Cells
    If cll.Value <> 0 Then
      AllZeroes = False
      Exit For
    End If
  Next cll
  If AllZeroes Then
    For Each cll In rw.Offset(, 4).Resize(, 4).Cells
      If cll.Value > 50 Then cll.Interior.ColorIndex = 3
    Next cll
  End If
Next rw
End Sub

Neither of these codes removes any existing colors.

[awale]

p45cal you my friend are an absalut legend and you have made my day cheers

[SamT]

Do not post duplicate threads. If you place it in the wrong forum, ask a moderator to move it for you. If you have "been at it" for days, it can wait for a moderator.

I have deleted the thread in the Forum Issues folder.

[awale]

alright cheers mate

it was in the wrong forum also this is the first time i asked a question but thanks anyways

[awale]

For Each rw In Range("A1:H22").Rows
If Application.Sum(rw.Resize(, 4)) = 0 Then
For Each cll In rw.Offset(, 4).Resize(, 4).Cells
If cll.Value > 50 Then cll.Interior.ColorIndex = 3
Next cll
End If
Next rw
End Sub


hello mate

if i have four more columns between the first four 0's and the 12 onwards (look at example below) and i wanted them to be ignored
how can i do that. see example below. will changing the 4's in the code you gave me be enough. will i need to extend the

0

0

0 0

0 0 0 0 0

12

39

24

96

[SamT]

will changing the 4's in the code you gave me be enough

Yes. Changing the first one will extend the zeros test

If Application.Sum(rw.Resize(, 4)) = 0 Then

The second one must be changed to match

For Each cll In rw.Offset(, 4).Resize

Changing the last one will extend the >50 test

).Resize(, 4).Cells

BTW, if you Select the code in your post, then click the # icon on the Menu, it will put CODE tags around your code and it will be nicely formatted when you post the message.

[p45cal]

if i have four more columns between the first four 0's and the 12 onwards (look at example below) and i wanted them to be ignored

Your example isn't very clear.
Maybe, just maybe, change:

For Each cll In rw.Offset(, 4).Resize(, 4).Cells

to:

For Each cll In rw.Offset(, 8).Resize(, 4).Cells

[awale]

once again great help and still a legend!
cheers mate

[awale]

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

[SamT]

Since you seem to be a serious student of VBA and Excell, I will just give you hints,.
InputBox is your friend

Name1 = InputBox "Enter First Name to Use in Box Below"
Name2 = InputBox "Enter SecondName to Use in Box Below"
Criteria = InputBox "Enter 1 or 0 to Use in Box Below"

Col1 = Range(NameRow).Find(Name1).Column
Col2 = Range(NameRow).Find(Name2).Column

For Each Cel in Col1
If Cel = Criteria AND Col2 Rows(Cel.Row) = Criteria THen
Color Me Cells Matey

[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

[excelliot]

where is your code??

[SamT]

@ All

the OP started a new thread on the same problem. I have moved the posts from that thread to this thread.

Please read the entire thread before posting.

[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.