PDA

View Full Version : NEW TO VBA NEED HELP PLEASE!! thank you :)



awale
08-24-2015, 06:49 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:

SamT
10-25-2015, 04:39 PM
You posted this to the wrong folder. I moved it to the Excel Help Forum. I hope it is not too late

jolivanes
10-25-2015, 11:03 PM
An example workbook might do wonders. (Code does not work very well on pictures)

jolivanes
10-25-2015, 11:22 PM
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