PDA

View Full Version : NEW TO VBA NEED HELP PLEASE!!



awale
08-19-2015, 03:58 AM
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("A1:D22")

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
08-19-2015, 05:38 AM
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 SubNeither of these codes removes any existing colors.

awale
08-19-2015, 06:18 AM
p45cal you my friend are an absalut legend and you have made my day cheers

SamT
08-19-2015, 06:43 AM
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
08-19-2015, 06:56 AM
alright cheers mate

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

awale
08-19-2015, 11:20 PM
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
08-20-2015, 06:06 AM
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
08-20-2015, 06:12 AM
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 ignoredYour example isn't very clear.
Maybe, just maybe, change:
For Each cll In rw.Offset(, 4).Resize(, 4).Cellsto:
For Each cll In rw.Offset(, 8).Resize(, 4).Cells

awale
08-21-2015, 04:48 AM
once again great help and still a legend!
cheers mate

awale
08-25-2015, 01:02 AM
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




:help:banghead::think:

SamT
08-25-2015, 05:34 AM
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
08-28-2015, 02:04 AM
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




:help:banghead::think:

excelliot
08-29-2015, 08:24 AM
where is your code??

SamT
08-29-2015, 11:07 AM
@ 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
09-03-2015, 12:43 AM
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.