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 haphazard way. To sort out this problem I have made a simple formula to auto arrange the serial no. Here is a simple example for you.
Here in the above picture I have made two example columns. First column is serial no and second one is for content or name. Added a filter on these two column.
I need to make formula to auto arrange the serial no like this.
So I have used a simple formula in place of serial no.
This formula is placed in the serial no column and it will be dragged down from first cell to last cell. Now whenever you filter the sheet then the serial no will be automatically arranged starting from 1.
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 tab and put a simple macro in it.
Dim rgb1, rgb2, rgb3 As Integer
rgb1 = InputBox(“Input the value of Red between to 255”, “Red”)
rgb2 = InputBox(“Input the value of Green between to 255”, “Green”)
rgb3 = InputBox(“Input the value of Blue between to 255”, “Blue”)
Range(“A1:XFD1048576”).Interior.Color = RGB(rgb1, rgb2, rgb3)