Excel Tip: An easier way to change cell colours

Before I begin, I would like to wish a Happy New Year to all of my readers and subscribers - as this is my first post of 2016.

I am starting the year in humble mode, as I have had something very simple pointed out to me that I can't believe I had never spotted, despite it being right under my nose!

In my last post of 2015, I showed you some code to swap one cell colour for another throughout a worksheet. I then received a comment from a reader that I only know as Jim, pointing out that Excel already has a very simple way of doing this!

It can in fact be done using Find and Replace.

This amazed me because I use Find and Replace all of the time and didn't know you could do this!

It is simple as this:

1. Highlight the range which you want to apply the changes to
2. Click Find and Select (the binoculars icon) on the Home Ribbon and choose Replace from the dropdown menu
3. Don't enter anything in the Find or Replace boxes, but click the Format button next to the Find box and on the Fill Tab, select the colour that you wish to change
4. Click the Format box next to the Replace box and on the Fill Tab, select the new colour that you wish to swap for the colour you selected above
5. Click Replace All

You can even start with the colour of an existing cell, using the dropdown on the format button, however be sure to remove any other formats other than Fill colour.

The thing that really shocked me is that these two big Format buttons have been right there in front of me every time that I've used Find and Replace since Excel 2003! And that's a lot of times!

In 2016, I am going to try to make sure that I constantly keep my eyes open for new ways to do things in Excel, instead of just relying on what I think I already know! And thanks again, Jim.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Merry Christmas with a quick macro to swap one colour for another

First of all I would like to wish all of my readers and subscribers, a very Merry Christmas and a Happy 2016.

I know we all work hard, and I hope, like me, you all get a chance to spend some time with those you love in the coming days, whether Christmas is a festival you celebrate or not.

One hassle I sometimes have when building spreadsheets for others is changing colours.

The spreadsheet may work perfectly, but the end user might not like the colours I have chosen. This isn't too much of a problem if we are talking about large blocks of colour, but it can be a time-taking process, if I have used yellow to denote input cells throughout the whole worksheet, and the client wants orange, for example!

When this happened recently on a particularly complex spreadsheet, I decided to write a little macro to help, which turned out to be pretty straightforward and does the job in seconds.

Here is the code of the macro.

Sub ColourSwap() 

Dim Source As Variant
Dim R As Integer
Dim G As Integer
Dim B As Integer
Dim NewColour As Variant
Source = ActiveCell.Interior.Color
If MsgBox("Switch to no colour?", vbYesNo) = vbYes Then
NewColour = 0
For Each cell In ActiveSheet.Cells
If cell.Interior.Color = Source Then cell.Interior.ColorIndex = NewColour
Next
Else
R = InputBox("R?")
G = InputBox("G?")
B = InputBox("B?")
NewColour = RGB(R, G, B)
For Each cell In ActiveSheet.UsedRange.Cells
If cell.Interior.Color = Source Then cell.Interior.Color = NewColour
Next
End If 

End Sub

The easiest way to include it in your spreadsheet is to hit Alt-F8 and type the name you want to call the macro in the box at the top of the dialog. I have called it ColourSwap. Click create and you will see the green sections above already showing in the window (with your chosen name replacing ColourSwap). Just paste the yellow section in between.

You can run the macro by hitting Alt-F8 again, selecting it and choosing Run. If you prefer, you can click options instead of Run, and assign a shortcut key to run it in future.

The macro works as follows, and is applied to a single worksheet at a time.

1. Click on one of the cells that contain the colour that you wish to swap;
2. Run the Macro
3. You will be asked if you wish to "Switch to no colour?", if you select "Yes", then all of the cells on that worksheet that are the same colour as the cell you selected, will have any Fill Colour removed. This is the same as choosing No Fill if you were colouring the cell manually.
4. If you select "No", you will be faced with 3 prompts, requesting the R, G and B values for the colour you wish to swap to. Once you enter these, all of the cells on the worksheet that are the same colour as the cell you selected, will be filled with this new colour.

NB: The macro only checks the Used Range of the worksheet, so will not change any coloured cells below, or to the right of the last populated cell on the worksheet. This is to save time, as it can take quite a while to loop through every cell, when this is not necessary. If (for some reason) you have coloured cells outside of the Used Range, then these should be very easy to change manually, as they will almost certainly be in a large block.

Note that if you wish to save the Macro with the spreadsheet, you will need to save the Workbook as Macro-enabled Workbook, however this will usually be unnecessary, as once the colours have been changed, the macro is no longer needed, so that you can let it save without the macro.

I hope you find this useful, and could maybe use it to make some tired old spreadsheets look a bit more festive!

Merry Christmas!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: How many calendar months does a date range affect?

This week I responded to a client request for a formula that seemed straight-forward, but it took a few emails backwards and forwards to establish exactly what was required.

My answers at each stage, provide a number of alternative versions of what appear to be the same thing - but aren't!

The question itself was essentially "How do I calculate the number of months between two dates?"

My first response to this question used the DATEDIF function as featured in this earlier post.

Assuming that the start date is in cell A1 and the end date is in cell A2, then:

=DATEDIF(A1,A2,"M")

will return the number of WHOLE months between the two dates.

My client then said that they wanted to always round up the number of months.

I then used the "MD" argument of a DATEDIF to identify the remaining days after calculating the whole months, so that I could use and IF statement to add 1 if this remainder was greater than zero:

=DATEDIF(A1,A2,"M")+IF(DATEDIF(A1,A2,"MD")>0,1,0)

Finally, it transpired that what was really required was the number of calendar months touched by the date range, e.g. if the start date was 31st January 2015 and the end date was 1st February 2015, the answer should be 2, as both January and February feature in the date range (with the same dates, our first example would return zero, as there are no whole months and our second would have returned 1, as we rounded the 2 days up to a whole month).

This required a completely different approach, by using the month function to pull out the month from each date, and the year function to pull out the year. The answer would then be the difference between the month numbers, plus 1, plus 12 * the difference in the years, i.e.

=(MONTH(A2)-MONTH(A1)+1)+((YEAR(A2)-YEAR(A1))*12)

Depending upon your specific needs, any one of these formulae might be correct for your requirement!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: An introduction to dynamic ranges

A few months ago I wrote a post  introducing the use of ranges in Excel:

Excel Tip: A short introduction to named ranges

In that post I alluded to dynamic ranges but didn't really say much about them, so I thought I would expand a little on the subject now.

As the name suggests, dynamic ranges are ranges that are not fixed. The range referred to varies based upon either the cell in which the reference is used or a variable (or variables) referred to in a formula, or both.

If we go to name a new range using the Name Manager on the Formulas ribbon (if you need a refresher on this, please visit my earlier post), and in the "Refers to" box, we highlight a range, then by default this range is absolute (Excel automatically inserts the dollar signs to fix the row and column references).

So, if we type "Test" in the Name field and delete the contents of the "Refers to" box, then click in cell A1 of Sheet1 (while still in the "Refers to" box), Excel populates the "Refers to" box with:

=Sheet1!$A$1

This means that if we refer to the range "Test" anywhere else in the workbook, it will look at the contents of cell A1 of Sheet1. This is a normal (fixed) range.

Now let's create a dynamic range.

We will follow the same process to create second range, but this time, ensure that cell B1 is selected before we start. Again create a new range in the Name Manager but call it Test2 this time. Delete the contents of the "Refers to" box again, then, again, click in cell A1. This will populate the "Refers to" box, as before, with:

=Sheet1!$A$1

This time though, before clicking OK, use the F4 key to remove the dollar signs.

The "Refers to" box should now read:

=Sheet1!A1

This is now a relative reference, relative to the cell we had selected prior to defining the range (B1). The range "Test2", will now refer to the contents of the cell immediately to the left of the cell that you use it in (as A1 is the cell immediately to the left of B1).

Try typing the formula =Test in multiple cells. This will always return the contents of cell A1. But type =Test2 in any cell, and it will return the contents of the cell to its left. "Test2" is therefore a dynamic range - the cell(s) it refers to change depending on where it is used.

Note that this is not just the case with a single cell, we can refer to a number of cells.

Let's take an example. Let's say that we have a 12 month budget, with the 12 monthly values in columns B to M. Click in cell N1 and name a new range in the Name Manager called "FullYear" and type the following into the "Refers to" box:

=Sheet1!$B1:$M1

We can either type this in, or as before, highlight the cells B1 to M1, but then use the F4 key on each of the references to remove the dollar from the row reference.

Note that I have left the dollars on the column references. This means that the row will change, but the columns will stay fixed. This means that we can use the formula =SUM(FullYear) and it will always sum columns B to M of the current row.

Sticking with this same example, we can use the OFFSET function to extend this further and allow us to have dynamic ranges for the current month's balance, and the year to date balance.

If we store the number of the current month (1 to 12) in cell P1, then we can name a range called "MonthBalance" by clicing in cell N1, and creating the range as before, but typing the following formula in the "Refers to" box:

=OFFSET($B1,0,$P$1-1)

Note the use of the dollars, The column of B1 is fixed but the row is left relative, whereas P1 is entirely fixed.

This will return the contents of the cell P1-1 cells to the right of column B, on the same row. So if the month number entered in P1 is 3, P1-1 =2. As column B is the month 1 balance, 2 columns to the right in column D is the month 3 balance!

So we can enter =MonthBalance in any cell and it will return the current month's balance from that row.

We can define the range "YTD" as:

=OFFSET($B1,0,0,1,$P$1)

This will return a range P1 wide and 1 row high starting in column B of the current row, i.e. all of the months to date. As this will usually be more than one cell, you will need to use SUM to total the range.

We can now use =SUM(FullYear), =MonthBalance and =SUM(YTD), to give us these totals on any row of the sheet. In addition, MonthBalance and SUM(YTD) will change depending on the month number entered in P1.





If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".