Lesson#128: Formula to make serial no auto arranged when filtered

Here I will show how to make auto arranged serial no when you filter a column. This post will be very useful to many excel users like me. Suppose you are using a data sheet and you need filter certain portion to make print out. When you filter the data, the serial number becomes in…

Lesson#127: Trick to make a color picker with VBA

An easy and simple trick to make a color picker will be shown here. Here I have made a color palette by coloring different cell with different colors. This palette allows us to pick different type of color by selecting cell. I designated a certain area where the chosen color will be shown. Now I…

Lesson#126: Making a simple color tester with VBA

Here I will show you how to make a simple color tester with VBA. This color tester is having only one big button. It will take input from the user for the value of Red, Green and Blue. Then it will color the whole worksheet. For this you have to insert a button from Developer…

Lesson#125: How to use function with VBA

Hello we are back again with a very basic VBA learning topic. How to use function with VBA. Here I am having an array with some random numeric values in the range A1:A10. I want to make SUM, AVERAGE, MAX, MIN in different cells and I will use VBA for that. So I have written…

Lesson#124: How to format text in excel with VBA

Another step towards learning VBA. In this post we will learn to format text in excel with VBA. In the previous post I discussed about changing cell background color and about color codes. So we will start in a little advance mode here. I am here taken an example of cell A1. I am going…

Lesson#123: How to change cell color with VBA

Here I will show how to change cell color by using VBA. These are the basic lessons for learning VBA. There are some methods to write a code to change cell background color. Here I have picked a cell range A1:A10 as an example. Now press Alt+F11 to go to VBA editor and click on…

Lesson#122: Cell formatting in a protected sheet

Excel doesn’t accept to format cells which are protected, if they aren’t allowed for users. To format unprotected or protected cells users must allow before protecting a sheet. When you are protecting a sheet just see these options. The third option is Format Cells and when you select this you will be able to format both protected…

Lesson#121: How to make some cells unprotected when protecting a sheet

OK, I am having a sheet here which calculate the age when birth date is provided. I need to protect the sheet before giving it to users. So none of them change the structure or formula. But I also need to┬ámake some cells unprotected to make the user enter necessary data. I have colored those…

Copyright 2017-2019@Excelabcd