Change font on all cell comments

This is a simple code that is very useful if you want to change the font size on all comments that you have on your Excel sheet.

Sub ChangeCommentsFont()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        With cmt.Shape.TextFrame.Characters.Font
            .Size = 11
        End With
    Next cmt
End Sub

You can change this code for setting a new font name, font color, etc.

Moving around Excel sheets

Excel lists all of your sheet names on the Sheets Tab, on the bottom of your page, all except the ones that are hidden. For example, you can have your sheets tab like this:

Sheetstab1

You can change your sheet just by clicking your sheet name on the sheets tab bar and if the sheet name isn’t visible, you can scroll using the little arrows located on the left side of your sheets tab. You can also more around your sheets by using Ctrl+Page Up or Ctrl+Page Down. On this example, we have a sheet for each month (January to December) but it only displays January to May. That is because there isn’t enough space to display all sheets names. You can adjust the space my moving the separator to the right to increase the space for the sheets names but it can happen that even so, there isn’t space for all of your sheets, as you can see on this example:

Sheetstab2

On this case, you can right click on one one the navigation arrows that are on the left side of your sheets tab and it will display a pop-up window with all of your sheets names where you can select the sheet to where you want to go. It will show up like this:

Sheetstab3

I bet that this little trick will save precious time when you’re working with large workbooks that contains dozens or even hundreds of sheets!

Change Worksheet Gridlines Color

By default, gridlines on Excel sheets are black but you can change this. So instead of having your sheet display black gridlines, like the ones on this image…
 
Gridline_Color1

…you can have your gridlines with another color, like this example:

Gridline_Color2
To change the gridlines color, just go to your File tab and select Options. Then on your Excel Options dialog window, select the Advanced tab and scroll down until you find Gridline color option. Just select the color you want and click the OK button and you’re done.

Quickly fill a range of cells with a value or a formula

If you want to fill a range of cells with a value, a formula or random numbers, there is a faster way of doing it using Ctrl+Enter instead of just hitting the Enter key. To fill a range of cells with a value, follow this steps:

1. Select the range of cells that you want to fill
2. Enter the value or formula
3. Instead of hitting Enter, press Ctrl+Enter

Here’s an example on how to fill a range of cells with random numbers using this tip:

1. Select cells A1:E10
2. Enter this formula: =RANDBETWEEN(1,10)
3. Press Ctrl+Enter

This will fill the range A1:E10 with random values between 1 and 10, like this:

FILL_RANGE

Count characters on a range of cells

You can use the LEN() function to get a count of characters from a value on a cell like this:

=LEN(A1)

But if you want to get a count of characters from a range of cells, if you try to do something like this =LEN(A1:B6) you will get an #VALUE error. The trick here is to use SUMPRODUCT() function to perform the count of characters using a formula like this:

=SUMPRODUCT(LEN(A1:B6))

You can see here, on this example, the result of this formula on a sample sheet.

COUNTCHARS

ERROR.TYPE() Function

This function returns a value corresponding to the type of Excel error. This is the syntax:

ERROR.TYPE(error_val)

error_val is a reference to a cell that has a formula that you want to check if it returns an error value. Here are the possible return values:

ERROR_TYPE1

This function can be very useful to check if a certain formula returns an error, so that you can display a custom error message to the user. For that you can use the IF() function combined with the CHOOSE() function, like this:

=IF(ERROR.TYPE(A2)<=8,CHOOSE(ERROR.TYPE(A2),"Ranges not intersecting","0 Divisor","Wrong data type","Invalid cell reference", "Unrecognized range or function name","Number error","Inappropriate function argument","Waiting for query data"),"")

This formula will check if there is an error on cell A2 formula and if it returns a ERROR.TYPE value will choose the appropriate error message to display on the cell where this formula is placed. If there is no error, it will return nothing. Here are some examples of the use of it:

ERROR_TYPE2

You can improve this formula to give you the cell address where you have the error by using the CELL() function, like this:

=IF(ERROR.TYPE(A3)<=8,CHOOSE(ERROR.TYPE(A3),"Ranges not intersecting","0 Divisor","Wrong data type","Invalid cell reference", "Unrecognized range or function name","Number error","Inappropriate function argument","Waiting for query data")& " on cell " & CELL("address",A3),"")

This will return a message like this instead:

ERROR_TYPE3

Hope this is useful to get your worksheets more user friendly.