In this blog post, we look at how to create a macro that will look in a list and format all the instances of specific words that appear in a cell.

This macro is case sensitive and will also only apply the formatting if it is the whole word. For example, if you are formatting all instances of the word ‘red’, this macro will not format it if used in words like ‘reddish’ and ‘sacred’.

The macro uses a For Each Next loop to work on the range of cells selected by a user.

The Characters property has been used to apply the formatting to only specific words in a cell. In order to work, the Characters property will need the position of the first character of the word, and also how many characters to format.

The InStr function was used to find the first character of the word, or words, in each cell.

A Do loop is used to check the whole cell so if the word occurs multiple times in a cell, each instance is formatted.

Here is the complete Excel VBA macro code used.

[Read more…] about Format Specific Words in a Cell using VBA

When a range of cells are selected, Excel automatically displays formula results on the status bar. This can be an incredibly useful feature of Excel, especially when combined with filtering data.

Excel can display the sum, average, max, min, count of cells containing values and count of cells that are not blank in the status bar.

This blog post, looks at how you can get Excel to display your own function on the status bar. For example, you may need to know the median or mode value, or count the number of blanks in the selected range.

In this post, we will get Excel to display the number of blank cells in the selected range on the status bar.

[Read more…] about Add Your Own Function to the Status Bar in Excel

I created a hangman game using Excel VBA for fun, and thought I would post it here for all budding Excel VBA learners to look at.

I find creating games, quizzes and sports spreadsheets an enjoyable way of keeping my Excel skills fresh, and to develop them further.

The game asks you to guess a letter to complete the phrase. A hint is provided for a little help, and the phrase will be related to music, sports, people, film or geography.

The spreadsheet is unprotected so you can check out the code and play around it with it further.

The VBA used to create this hangman game includes;

• Dynamic arrays.
• Creating a userform.
• Module scoped variables.
• Lots of text work including functions such as Left and StrConv.
• Lots of For Loops, IF statements and a Select Case statement.

Download the Excel Hangman game

I also have a special Christmas version of my Hangman game in Excel to download.

In modern version of Excels, when downloading macro enabled spreadsheets, you will often need to unblock the files to allow the macros to run.

Do this by;

• Locate the Excel file on your computer (probably the Downloads folder).
• Right-click on the file and click Properties.
• Check the Unblock box and click OK.

Let me now how you do? I want to see some high scores 😉

Want to improve your Excel VBA skills? All the techniques used to create this hangman game are covered in our Excel VBA course.

[Read more…] about Create a Hangman Game in Excel