Up next in 10
Personalize/Customize Active Cell Formatting in Excel Using VBA
4K views
Dec 4, 2024
Learn how to customize the look of the active cell in Excel using VBA! This tutorial use the Worksheet_SelectionChange event, which triggers whenever the active cell changes. By using this event, we'll dynamically apply custom formatting to the active cell, such as background colors, gradients, font styles, and sizes. You'll also discover how to restrict these customizations to specific ranges or apply them across the entire worksheet. The Worksheet_SelectionChange event makes it easy to automate these changes seamlessly, enhancing the focus and usability of your Excel sheets.
View Video Transcript
0:00
hi everyone and welcome in this video
0:02
we'll show you how to customize the look
0:04
of the active cell in Excel by default
0:07
an active cell or range has a green
0:09
border and a fill handle but there's no
0:11
built-in option to change its appearance
0:13
using a simple VBA trick we'll show you
0:15
how to customize the formatting of the
0:17
active cell as seen here you can apply
0:20
this customization to a range sheet or
0:22
the entire workbook let's dive in and
0:25
learn how to make this happen to write
0:26
the VBA code open the VBA editor by
0:30
right clicking the sheet name at the
0:31
bottom of excel choose view code and the
0:33
editor for that sheet will open in the
0:35
editor you'll see two dropdowns labeled
0:38
General and declarations click the
0:40
general dropdown and select worksheet
0:42
then choose selection change from the
0:44
second drop down this creates a sub
0:46
routine called worksheet uncore
0:47
selection change inside this sub routine
0:50
insert the code as shown on the screen
0:52
go back to Excel and try moving the
0:55
active cell you'll see the background
0:56
color change the worksheet uncore
0:58
selection change event is triggered
1:00
whenever the user selects a different
1:02
cell or range on a worksheet it allows
1:05
you to run specific code whenever a
1:07
selection change occurs such as changing
1:09
formatting based on the new selection
1:11
but there's an issue the background of
1:13
the previous active cell is not reset to
1:16
fix this return to the VBA editor and
1:19
add this line at the beginning of the
1:21
sub routine this resets the background
1:23
of all cells to the default color before
1:25
applying the new formatting to the
1:27
active cell now only the current an
1:30
active cell will display the custom
1:31
formatting if you want to restrict this
1:33
Behavior to a specific range use the if
1:36
statement open the VBA editor again and
1:39
insert the if statement at the beginning
1:40
of the sub routine as shown on the
1:42
screen this condition ensures that the
1:44
formatting is applied only when the
1:46
active cell is within the specified
1:48
range A1 to C10 if the active cell is
1:51
outside this range the default
1:53
formatting is maintained switch back to
1:56
Excel and test it now the custom
1:58
formatting will appear only for active
2:00
cells within the specified range this
2:03
simple technique using just a few lines
2:05
of code customizes the active cell
2:07
formatting and provides control over
2:09
where the changes apply so far we've
2:12
covered how to change the background
2:13
color of the active cell and reset it
2:15
when the cell changes you can also
2:17
customize the text or value formatting
2:20
of the active cell or selection to do
2:22
this go back to the VBA editor and
2:24
insert the code shown on the screen
2:26
inside the sub routine to make the
2:27
active cells text bold and italic
2:30
return to Excel and you'll see that the
2:32
value in the active cell within the
2:34
specified range is now bold and italic
2:37
however a similar issue arises the
2:39
formatting of the previous active cell
2:41
doesn't reset to fix this go back to the
2:44
VBA editor and add these lines at the
2:46
beginning of the sub routine to reset
2:48
the bold and italic formatting for all
2:50
cells in the range switch back to Excel
2:53
and explore the sheet you'll now notice
2:55
that the bold and metallic formatting is
2:57
applied only to the current active cell
2:59
while the formatting of the previous
3:01
cell is reset the functionality works
3:03
perfectly for the specified range before
3:06
moving further let's optimize our code
3:08
currently we use me do range repeatedly
3:11
in lines that reset formatting if we
3:14
need to update the range from a one C10
3:16
to another range we'd have to modify
3:19
every instance manually to simplify this
3:21
we can Define the range once using the
3:23
code shown on the screen then replace
3:26
every instance of me. range with RNG
3:29
this allows us to update the range in
3:31
one place making the code more
3:33
maintainable and scalable you can add as
3:36
many formatting options as you need for
3:38
the active cell let's quickly add a line
3:40
of code to increase the font size of the
3:42
active cell's value as shown on screen
3:44
to ensure the font size resets for cells
3:46
that are no longer active add this line
3:48
at the start of the sub routine to reset
3:50
the font size for all cells in the range
3:53
now return to Excel and explore the
3:55
sheet you'll notice that the font size
3:57
of the active cell increases while the
4:00
formatting of previous active cells
4:02
resets correctly finally let's add a
4:04
gradient background to the active cell
4:06
insert this code inside the sub routine
4:09
this code applies a gradient fill
4:11
pattern to the active cell or range
4:13
transitioning from yellow to light blue
4:16
at a 45° angle switch back to Excel and
4:19
you'll see a beautiful gradient
4:21
background applied to the active cell
4:23
this enhances the visual effect and
4:25
makes the active cell stand out even
4:27
more and that's it customizing the ACT
4:29
active cell in Excel enhances focus and
4:32
usability with VBA you can apply unique
4:35
formatting like colors gradients and
4:37
font Styles tailored to specific ranges
4:39
or the entire sheet this simple trick
4:42
adds a dynamic and personalized touch to
4:44
your Excel experience thank you for
4:46
watching please share this video and
4:48
don't forget to like subscribe and hit
4:50
the notification Bell for more Excel
4:52
tips and tutorials your support helps us
4:54
create more valuable content See you in
4:56
the next video