1
00:00:01,170 --> 00:00:06,510
Hello and welcome back to another class of, of course, about the complete introduction to VBA with

2
00:00:06,510 --> 00:00:07,690
Microsoft Excel.

3
00:00:08,490 --> 00:00:12,330
So in the past few classes, we talked about plenty of things.

4
00:00:12,340 --> 00:00:17,750
For example, in the past class, we talked about how to move and, well, how to basically move on,

5
00:00:17,760 --> 00:00:20,190
excel and work with ranges and all this kind of stuff.

6
00:00:21,150 --> 00:00:25,770
And we basically talked about a lot of things that you guys can do with VBA.

7
00:00:26,220 --> 00:00:32,460
So what I want to cover today that we didn't cover as well, and I think it's pretty important to cover.

8
00:00:33,120 --> 00:00:35,910
It's basically the use of colors for you.

9
00:00:35,910 --> 00:00:39,890
Well, for the purpose of creating your applications or your macros.

10
00:00:40,350 --> 00:00:47,670
So it's say you guys want to add some colors to your macro or to yourself how exactly you can do this

11
00:00:48,060 --> 00:00:52,140
and basically what type of code you will need to write down.

12
00:00:52,170 --> 00:00:57,180
So, yes, we can, of course, use macros, but for the purpose of this course, we are going to write

13
00:00:57,180 --> 00:00:59,670
it in the form of a code.

14
00:01:00,960 --> 00:01:03,110
So what I propose you guys to do is pretty simple.

15
00:01:03,960 --> 00:01:07,170
I want to practice what I want to practice also what we have learned.

16
00:01:07,170 --> 00:01:15,960
So basically we are going to create a basic, absolute function and what an absolute Maku that will

17
00:01:15,960 --> 00:01:17,160
work on a cell.

18
00:01:17,160 --> 00:01:25,860
And then we're going to create a macro that is relatively, well, relevant reference, relative reference,

19
00:01:26,100 --> 00:01:28,580
and basically it will be applied everywhere.

20
00:01:29,400 --> 00:01:35,160
And also what I want to add I want to add some basic things, such as a bold italic and all the stuff

21
00:01:35,160 --> 00:01:43,290
like we always do, because I want to use the with the element in this in discourse.

22
00:01:43,860 --> 00:01:47,820
So you'll see we're going to divide our code in to put it in two parts.

23
00:01:48,270 --> 00:01:48,560
All right.

24
00:01:48,570 --> 00:01:49,390
So let's start.

25
00:01:49,710 --> 00:01:53,970
So let's always first thing that you guys will need to do is simply open up your visual basics.

26
00:01:54,450 --> 00:02:00,290
And as always, you go insert and create yourself a module for this macro.

27
00:02:01,350 --> 00:02:07,200
So as always, when it's all done you guys want to do right now is create your procedure.

28
00:02:07,220 --> 00:02:12,360
So it's pretty important to create our procedure because if we don't have one, we will not be able

29
00:02:12,360 --> 00:02:15,160
to work well to create our market.

30
00:02:15,840 --> 00:02:21,720
So let's call let's call it color, because we're going to create colors.

31
00:02:23,070 --> 00:02:23,520
All right.

32
00:02:23,520 --> 00:02:24,000
Amazing.

33
00:02:24,660 --> 00:02:29,280
Right now, what we are going to do is what what we're going to do is pretty simple.

34
00:02:29,580 --> 00:02:31,350
I want to take just a cell.

35
00:02:31,350 --> 00:02:33,030
So basically take a simple cell.

36
00:02:34,110 --> 00:02:36,830
And in this cell, I want to add color.

37
00:02:36,840 --> 00:02:39,840
So basically, I want to make the whole cell colorful.

38
00:02:40,410 --> 00:02:47,010
So like this, for example, or and I want to make the text inside of myself colorful as well.

39
00:02:47,020 --> 00:02:52,890
So here in this case, it's yellow and red and that's what we're going to do in this class.

40
00:02:52,890 --> 00:02:56,520
We're going to talk about what basically how you can do this.

41
00:02:57,360 --> 00:03:02,550
But not only this, we're going to talk about two ways that you guys can write down what you guys can

42
00:03:02,550 --> 00:03:09,630
write colors in Microsoft Excel with the use of VBA, because there is more than one way, because there

43
00:03:09,630 --> 00:03:11,370
is a lot of different colors.

44
00:03:12,060 --> 00:03:17,940
So a pretty simple first thing that we want to do is basically select the range in which we want to

45
00:03:17,940 --> 00:03:18,180
work.

46
00:03:18,190 --> 00:03:19,590
So in this case, we want to work.

47
00:03:19,590 --> 00:03:22,500
Let's say we want to work on the range that is here.

48
00:03:22,500 --> 00:03:23,530
So desex.

49
00:03:24,000 --> 00:03:26,790
Well, it's he said, well, let's say desex.

50
00:03:27,570 --> 00:03:34,920
So write down range and arrange will be the six.

51
00:03:38,470 --> 00:03:40,900
And what to do exactly with this range.

52
00:03:40,930 --> 00:03:45,130
We want to select it at first, so we will select this range at first.

53
00:03:46,030 --> 00:03:52,050
So when it's all done, what do we want to write down something inside of this range?

54
00:03:52,060 --> 00:03:59,110
So we're going to use the selection function and we're going to integrate in the Formula or one C1.

55
00:03:59,920 --> 00:04:05,110
So in this case, it's possible to write it down in two ways because I want to add some color inside

56
00:04:05,110 --> 00:04:10,760
of it, inside of it, so we can write down a selection, then use the width, too.

57
00:04:10,780 --> 00:04:19,840
So basically with and with to not write down, not rewrite selection two times, but in this case we

58
00:04:19,840 --> 00:04:21,090
will write it down this way.

59
00:04:21,400 --> 00:04:28,660
So we rewrite it down to two times the in the second part we are going to use the with two.

60
00:04:30,430 --> 00:04:31,210
So pretty simple.

61
00:04:31,220 --> 00:04:37,870
What we what we need to do at first we to select our what we need to select the formula that we are

62
00:04:37,870 --> 00:04:38,740
going to work with.

63
00:04:38,740 --> 00:04:45,130
And this is going to be our once you want the formula that is used to add text or functions inside of

64
00:04:45,130 --> 00:04:45,980
our cell.

65
00:04:46,720 --> 00:04:49,060
So until now, you guys should know it.

66
00:04:49,060 --> 00:04:51,300
But if you don't, it's not a big deal.

67
00:04:51,790 --> 00:04:53,940
You can check out the first few classes.

68
00:04:54,700 --> 00:05:00,990
So Formula R one, see what and what exactly we want to do.

69
00:05:01,780 --> 00:05:05,800
We want to add a test, so we'll write down test.

70
00:05:06,700 --> 00:05:07,040
All right.

71
00:05:07,060 --> 00:05:11,770
So when it's all done, it's time to add some colors to our test.

72
00:05:12,240 --> 00:05:15,490
Well, to our our cell right there.

73
00:05:15,940 --> 00:05:19,780
So basically, you can write well, you can add two types of color.

74
00:05:19,990 --> 00:05:22,330
You can color the whole cell.

75
00:05:22,540 --> 00:05:27,010
And for this in this case, you will use the function interior.

76
00:05:27,280 --> 00:05:33,730
So interior color or you can simply color the text that is inside of the cell.

77
00:05:33,760 --> 00:05:37,420
So in this case, you simply use that font and color.

78
00:05:38,860 --> 00:05:45,610
Then not only this, there is more than one way that you guys can write down colors on Microsoft Excel

79
00:05:45,610 --> 00:05:46,480
with the use of GIBI.

80
00:05:46,930 --> 00:05:49,340
You can write it down as the code of color.

81
00:05:49,360 --> 00:05:55,270
So basically, by using the code of color or simply by calling your colors, let's say you want to call

82
00:05:55,270 --> 00:05:57,760
the blue, you will write down Vehbi blue.

83
00:05:58,820 --> 00:06:00,710
Once again, that's another example.

84
00:06:01,790 --> 00:06:08,150
So let's try it out, so the first thing that we'll do will simply use the code of color for our first

85
00:06:08,150 --> 00:06:08,780
example.

86
00:06:08,810 --> 00:06:11,810
So in this case, we will write down selection.

87
00:06:14,010 --> 00:06:19,350
That interior, because we are going to work with interior that color.

88
00:06:21,190 --> 00:06:27,670
And in this case, then you simply write down equal and you want to write down the code of your color.

89
00:06:27,690 --> 00:06:30,240
So I want to write down your random number.

90
00:06:30,760 --> 00:06:34,270
So basically, I don't know what I can write.

91
00:06:35,440 --> 00:06:35,920
Here we go.

92
00:06:36,850 --> 00:06:39,340
So you can write down a random number.

93
00:06:39,340 --> 00:06:45,790
I suggested write down big numbers like this one somewhere in number that is in the millions because

94
00:06:45,790 --> 00:06:47,410
this is where the colors are.

95
00:06:48,610 --> 00:06:56,830
So if we run our well, just the first part of our code, as you can see, this is the color that we

96
00:06:56,830 --> 00:06:57,340
get.

97
00:06:57,370 --> 00:06:58,150
So basically.

98
00:06:59,070 --> 00:07:02,130
It's red, so Texas red just put it black.

99
00:07:02,970 --> 00:07:03,610
So here we go.

100
00:07:03,660 --> 00:07:07,350
This is the color that we get to the.

101
00:07:09,600 --> 00:07:16,440
If want to change our color, we can do it to just change the numbers we can instead of can write down

102
00:07:16,440 --> 00:07:18,360
something else and rerun it.

103
00:07:18,810 --> 00:07:20,520
And as you can see, the color will change.

104
00:07:20,970 --> 00:07:24,230
So how exactly you can know the code of your color?

105
00:07:24,660 --> 00:07:25,700
Well, it's pretty simple.

106
00:07:25,740 --> 00:07:31,770
What I suggest you guys to do is simply go on Google and write it down and find out all the different

107
00:07:31,770 --> 00:07:35,040
types of colors or it's going to be somewhere right there.

108
00:07:35,070 --> 00:07:39,210
So basically here you have all the different types of colors that are possible.

109
00:07:40,770 --> 00:07:42,030
Which is once again pretty cool.

110
00:07:42,060 --> 00:07:44,220
So those are all the colors that exists.

111
00:07:48,050 --> 00:07:51,680
All right, so this is what we have done until now.

112
00:07:52,100 --> 00:07:53,850
So what is the next step?

113
00:07:53,870 --> 00:07:57,020
So the next step for all this is pretty simple.

114
00:07:57,800 --> 00:08:00,740
We want to change the color of our text.

115
00:08:01,070 --> 00:08:03,230
So how exactly we will do this?

116
00:08:03,680 --> 00:08:04,460
So pretty simple.

117
00:08:04,460 --> 00:08:07,350
In this case, the function will be a little bit more different.

118
00:08:07,670 --> 00:08:14,810
So we are going to write down what instead of writing the interior color will just write down selection

119
00:08:14,810 --> 00:08:15,490
that color.

120
00:08:15,920 --> 00:08:22,310
But what I want to do as well is I want to change my text and put it bold and put it italic as well.

121
00:08:22,340 --> 00:08:26,530
So what I will do, I will use that with the element.

122
00:08:27,050 --> 00:08:34,400
And in this case, what the element will allow me to do is it will allow me to not be write selection

123
00:08:34,880 --> 00:08:37,090
like I did right there more than once.

124
00:08:37,700 --> 00:08:39,630
So I'll just write down selection.

125
00:08:40,820 --> 00:08:46,850
So I'll start with with and in this case, what I want to use is selection.

126
00:08:49,970 --> 00:08:57,240
And what I want to select on the select front, so we'll work with the fund that is right there, and

127
00:08:57,650 --> 00:09:00,780
then I can write what exactly I want to work with.

128
00:09:01,040 --> 00:09:04,720
So in this case, I'd say I want to work with the color advocates.

129
00:09:04,730 --> 00:09:06,250
So I want to change the color.

130
00:09:06,770 --> 00:09:08,300
So I'll start by changing the color.

131
00:09:09,380 --> 00:09:09,760
All right.

132
00:09:10,010 --> 00:09:18,260
So right now, what we'll do, we are going to change the color in a way that well, in another way.

133
00:09:18,560 --> 00:09:23,540
So instead of writing down the code of the color, we're simply going to use the stick in the way that

134
00:09:23,540 --> 00:09:25,540
I told you that it's possible to work with colors.

135
00:09:26,030 --> 00:09:31,040
We're going to write down VB and then the color that we want.

136
00:09:31,400 --> 00:09:33,690
So in this case, we want to have red.

137
00:09:34,130 --> 00:09:35,690
So let's say we want to use red.

138
00:09:37,670 --> 00:09:42,020
So finally, just write and with.

139
00:09:44,410 --> 00:09:45,750
They were great.

140
00:09:46,390 --> 00:09:51,690
So if you run our function, what's going to happen is that inside of this case, Artex will become

141
00:09:51,700 --> 00:09:52,090
red.

142
00:09:52,450 --> 00:09:55,360
So if we run it, as you can see, the text became red.

143
00:09:55,690 --> 00:09:57,880
And now let's say we want to add more features.

144
00:09:57,910 --> 00:10:02,410
Well, since we work with the with that is right there, we can add more features.

145
00:10:02,440 --> 00:10:10,390
So in this case, that color, we can add that all we can add that italic.

146
00:10:11,710 --> 00:10:16,180
And it's important to write down true in both of them if we want them to work.

147
00:10:20,930 --> 00:10:22,170
There we go.

148
00:10:22,820 --> 00:10:27,830
All right, now we can run everything to see what it looks like, says can see your became bold and

149
00:10:27,830 --> 00:10:29,280
it became in italics.

150
00:10:29,300 --> 00:10:34,910
And we also changed the color of our text, which is pretty cool in my opinion.

151
00:10:35,840 --> 00:10:39,980
So right now, you know how to basically change the colors of your text.

152
00:10:40,340 --> 00:10:40,700
All right.

153
00:10:40,700 --> 00:10:47,530
Let's say right now we want to be able to work with this function, but not on this range.

154
00:10:47,540 --> 00:10:49,760
We can just delete the range.

155
00:10:51,050 --> 00:10:55,070
And now what's going to happen was simply go somewhere.

156
00:10:57,780 --> 00:11:03,570
And when we run our function, it will automatically generate everything, so basically, instead of

157
00:11:03,570 --> 00:11:07,120
having a range where this will work, it will work everywhere.

158
00:11:07,140 --> 00:11:12,450
So basically, if we're on the cell and we run our function, it's going it's going to work.

159
00:11:12,720 --> 00:11:16,120
If we go in another cell, it's going to work as well.

160
00:11:16,410 --> 00:11:24,960
So as you can see right now, simply by deleting the range, what we did is we have made our function

161
00:11:25,680 --> 00:11:27,610
not absolute, but relative.

162
00:11:27,630 --> 00:11:33,640
Well, we created a relative reference to our function, which is pretty cool.

163
00:11:33,660 --> 00:11:36,820
So you can see this is how you can change the colors.

164
00:11:36,850 --> 00:11:38,010
Once again, those are the basics.

165
00:11:38,700 --> 00:11:43,020
You can change colors of a huge amount of cells at once.

166
00:11:43,680 --> 00:11:46,860
You can change colors of just one cell, of two cells of three cells.

167
00:11:46,860 --> 00:11:47,880
You decided.

168
00:11:48,180 --> 00:11:50,850
And right now you know how to implement this.

169
00:11:51,690 --> 00:11:56,750
Well, this element inside of your Excel VBA programming.

170
00:11:57,060 --> 00:12:00,480
So as it first starts, guys, and all in our next class.