January 22, 2006

Gridlines And Borders

This is probably the easiest way to highlight the active cell and it requires no VBA or even conditional formatting.

Look at this picture,

And this one,

And this one.

See the difference? By changing the borders, the active cell becomes surrounded by color. In fact, if you use double lines or thicker lines, the color becomes even more visible. The best colors to highlight seem to be medium colors such as Teal or Blue, but darker colors also work (don't go for the grays though) and they usually show as "black" when printing anyway.

Nice. But before you start trying this out and changing all of the Borders...

Automatic As Default
This took a while to figure out - The default setting for Gridline colors is "Automatic" (the pale gray lines you see when you open a new workbook) and the default setting for Border is Automatic also where they will show as "black".

While the Borders are in the default setting, you can change the Gridlines color by going to Tools, Options, View at the top menu and the Borders will also change to the same color. Even if you untick the Gridlines checkbox to hide them, the Borders will still show as the new color.

This is a sheet wide change. To do this with VBA is simple enough. (There appears to be an RGB version too though I've not tried it yet, this method uses the same color index as Interior and Font colors)

Sub ChangeGridlineColorSheet()
    On Error Resume Next
    ActiveWindow.GridlineColorIndex = 3 ' change color to suit
End Sub

So for every sheet in a workbook, we can use this...

Sub ChangeGridlineColorBook()
    On Error Resume Next
    Dim ws As Worksheet, tSheet As Worksheet
    Set tSheet = ActiveSheet
    For Each ws In ActiveWorkbook.Worksheets
        ActiveWindow.GridlineColorIndex = 3 ' change color to suit
End Sub

I've even applied this to an application class event at work, running the above subroutine from a Workbook Open event. The advantage is any workbook I open, mine or not, changes the Gridline color to suit. The downside is it takes a little longer to open, lucky the workbooks at my company tend be to rather small. You should give the implications some serious thought before you try it though. (Don't use Change or Selection Change for an application wide class event, this interferes with the ability to paste)

But, how about if the Borders are not set as Automatic? Is there an easy way to change the color of all the Borders and still retain existing line weight and patterns?

Sure. Run this code for a selection.

Sub ChangeBorderColorSelection()
    On Error Resume Next
    Dim c As Range, i As Integer
    If TypeName(Selection) <> "Range" Then Exit Sub
    Application.ScreenUpdating = False
    For Each c In Selection
        With c
            For i = 1 To 8
                If .Borders(i).ColorIndex <> -4142 Then _
                    .Borders(i).ColorIndex = 3
        End With
    Application.ScreenUpdating = True
End Sub

You can adapt this code to work with sheets and books, but consider that really big files make take a long time as the code has to run through each and every cell. In my case, just a minute or two per book so no big deal. (Don't forget to save first just in case!)

For Sheets,

Sub ChangeBorderColorSheet()
    On Error Resume Next
    Dim c As Range, tRange As Range, i As Integer
    Application.ScreenUpdating = False
    Set tRange = Selection
    For Each c In Selection
        With c
            For i = 1 To 8
                If .Borders(i).ColorIndex <> -4142 Then _
                    .Borders(i).ColorIndex = 3 ' Change color here
        End With
    Application.ScreenUpdating = True
End Sub

And for books,

Sub ChangeBorderColorBook()
    On Error Resume Next
    Dim ws As Worksheet, tSheet As Worksheet
    Dim c As Range, tRange As Range, i As Integer
    Set tSheet = ActiveSheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        Set tRange = Selection
        For Each c In Selection
            With c
                For i = 1 To 8
                    If .Borders(i).ColorIndex <> -4142 Then _
                        .Borders(i).ColorIndex = 3 ' Change color here
            End With
    Application.ScreenUpdating = True
End Sub

And each and every book in a folder? Well that can be done too. But it's time to watch the Sunday night movie ;-)

Sometime later: Ah, the movie is boring. Use this code to get the color index (some colors are repeated)

Sub ShowColorIndex()
    On Error Resume Next
    Do While ActiveCell.Row < 57
        With ActiveCell
            .Interior.ColorIndex = .Row
            .Offset(, 1) = .Row
        End With
    Cells(1, 1).Select
End Sub


Update: I did some further experimenting...

My color "suggestions" are as follows,

Teal (Color Index 14) for Workbooks that don't get printed, the "red" lines show up rather well depending on your cell color.

In the case your Borders are normally "black", Dark Blue (Color Index 11) for Workbooks that do get printed (very close to black depending on the ink used, whether you use black/white or color), Dark Teal isn't too bad either, (Color Index 49)  

Posted by andrewe at 21:16

November 09, 2005

Another Approach To Formatting

Here's a question I was asked last week by Kelly (Hi Kelly!)

"I need to know how to format numbers so the decimal automatically comes up. For example, if I key 123456--I need 56 to automatically go into the decimal place."

And my reply (after cleaning the code up :-) )

Enter this into the appropiate sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Dim myDecimalPlace As Integer
    myDecimalPlace = Len(Target) - Len(Application.Substitute(Target, "-", ""))
    If myDecimalPlace > 0 Then
        Application.EnableEvents = False
        Target = Application.Substitute(Target, "-", "")
        Target = Target / Application.Power(10, myDecimalPlace)
        Target.NumberFormat = "0." & Application.Rept("0", myDecimalPlace)
        Application.EnableEvents = True
    End If
End Sub

I thought this was interesting because the "formatting" of the Target consists of 2 separate parts. The part to actually format, and the part to set the format and be deleted thereafter.

So intead of using something like a Custom Format or "Case 10 To 20" in code, the formatting doesn't depend on the value but on how you type it.

123- will result in 12.3 but 123-- will result in 1.23. This is far more simpler than trying to format each indivdual cell. (Ugh!)

Needless to say, other kinds of formatting are also possible.

Target.Font.Bold = True

Target.Interior.ColorIndex = 34

etc, etc, etc. Add them as you like - have fun ;-)  
Posted by andrewe at 02:49

August 07, 2005

Find And Replace In Conditional Formatting

When working with Conditional Formatting, I often find myself wanting to replace just part of a format but this can be quite difficult and/or time consuming depending on how everything is set up. (I'm not sure if this already exists within Excel's built-in features or whether somebody else has devised a better way. Whatever. This is something I made last week to make life more bearable)

The code below is limited to working with "Formula Is" type formatting and cell background colors and font colors. Note: All other formats such as borders (lines) will be lost forever. If you want to retain them, feel free to change the code as you like. You might want to save and check it works properly before you use it on a large scale.

Sub FindAndReplaceConditionalFormat()
    On Error Resume Next
    Dim c As Range, myRange As Range
    Dim fndValue As String, rplValue As String
    Dim frmtString1 As String, frmtString2 As String, frmtString3 As String
    Dim frmtColor1 As Long, frmtFont1 As Long
    Dim frmtColor2 As Long, frmtFont2 As Long
    Dim frmtColor3 As Long, frmtFont3 As Long
    fndValue = InputBox("Please enter the find value.", "Find And Replace")
    If fndValue = "" Then Exit Sub
    rplValue = InputBox("Please enter the replace value.", "Find And Replace")
    If rplValue = "" Then Exit Sub
    Set myRange = Selection
    Application.ScreenUpdating = False
    For Each c In myRange.Cells
        frmtString1 = Application.Substitute(c.FormatConditions(1).Formula1, fndValue, rplValue)
        frmtColor1 = c.FormatConditions(1).Interior.ColorIndex
        frmtFont1 = c.FormatConditions(1).Font.ColorIndex
        frmtString2 = Application.Substitute(c.FormatConditions(2).Formula1, fndValue, rplValue)
        frmtColor2 = c.FormatConditions(2).Interior.ColorIndex
        frmtFont2 = c.FormatConditions(2).Font.ColorIndex
        frmtString3 = Application.Substitute(c.FormatConditions(3).Formula1, fndValue, rplValue)
        frmtColor3 = c.FormatConditions(3).Interior.ColorIndex
        frmtFont3 = c.FormatConditions(3).Font.ColorIndex
        c.FormatConditions.Add Type:=xlExpression, Formula1:=frmtString1
        c.FormatConditions(1).Interior.ColorIndex = frmtColor1
        c.FormatConditions(1).Font.ColorIndex = frmtFont1
        c.FormatConditions.Add Type:=xlExpression, Formula1:=frmtString2
        c.FormatConditions(2).Interior.ColorIndex = frmtColor2
        c.FormatConditions(2).Font.ColorIndex = frmtFont2
        c.FormatConditions.Add Type:=xlExpression, Formula1:=frmtString3
        c.FormatConditions(3).Interior.ColorIndex = frmtColor3
        c.FormatConditions(3).Font.ColorIndex = frmtFont3
    Application.ScreenUpdating = True
End Sub

Summer holidays are approaching so I might take a couple of weeks off. I'll still be hanging around as I usually do though :-)  
Posted by andrewe at 16:39

June 28, 2005

Highlight Blank Fields

This is something I use as a reminder when something needs to be filled out in a form (invoices etc made on Excel spreadsheets).

From the main menu, choose Format, Conditional Formatting, Cell Value Is and enter ="". Then push the Format button on the right to select a suitable "highlight" of choice.

By highlighting these "must fill out" fields (cells) when blank, it's kind of hard to miss the obvious.

If you find yourself using this a lot, the following code makes it just a little faster by assigning a macro button. (Keep in mind it will delete any existing conditional formats first)

Sub HighlightBlankCells()
    On Error Resume Next
    Application.ScreenUpdating = False
    Dim c As Range, myRange As Range
    Set c = ActiveCell
    Set myRange = Selection
    For Each c In myRange
        With c
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="="""""
            .FormatConditions(1).Interior.ColorIndex = 4
        End With
    Application.ScreenUpdating = True
End Sub

For the "purists" you can use Formula is =ISBLANK(INDIRECT(ADDRESS(ROW(),COLUMN()))) to pick up unwanted "fluff" (to make sure cells are truly blank), generally speaking though Cell Value Is ="" should work just fine ;-)  
Posted by andrewe at 21:19

September 20, 2004

Errors and Zeros Revisited

I've posted about hiding errors and zeroes before, but here's a few more ways that you can use.

Hide Errors by Format
Select your range of cells, then right click to access the Format Cells dialog box.

Select the Number tag, then enter this Custom format.


Then change your font colour to the same colour as your cell background.

Hide Errors by Formula
The problem with using formats as above is that the font will appear if the cell background colour changes.

One way to overcome this is to add ISERROR to the formula you are using like this.

=IF(ISERROR(your formula),"",your formula)

Hide Zeros by Format
Select your range of cells, then right click to access the Format Cells dialog box.

Select the Number tag, then enter this Custom format.


Don't forget the second semicolon.

Hide Zeros by Formula
As shown above with ISERROR, you can also hide zeros by adapting your formula like this.

=IF((your formula)=0,"",your formula)
Posted by andrewe at 23:35

August 04, 2004

Superscript and Subscript

One thing I always found hard was how to shrink characters such as used with equations, measurements or chemical compounds. For example, how can I show cubic meters as a number?

It's quite simple when you use Superscript and Subscript. Enter the cursor into the cell and drag across the character(s) you want to select. Then right click and select Format Cells.

In the Effects section, check Superscript or Subscript as you prefer.

Here's a few of things you can do.

Have a look at the third one down. To get a degree symbol, I took an easy way out and used a small "o" (opposite of a capital "O").

Unfortunately Superscript and Sunscript don't work well with formulas. In this case, you can try inserting a degree symbol (Insert, Symbol for recent Excel versions) and then using a formula such as =A1&"°". You can also try using the CHAR function (Please note that some symbols appear to vary according which language version of Excel you use)

Superscript and Sunscript doesn't seem to work with numbers only either (all text, or text combined with numbers is fine). So how did I get the fourth one down?

I was sneaky. I formatted the cell as text first. :-)  
Posted by andrewe at 22:23

July 03, 2004

Back to Numbers

In the case that you want to convert numbers formatted as text back to numbers again, try using Excel's Error Checking features (To the best of my knowledge this is available from XP onwards)

Open the Options dialog box from the Tools menu, then select the Error Checking tab. If the Enable background error checking and Numbers stored as text checkboxes are ticked, then numbers formatted as text will appear with an error marker, (usually green, my Excel 2003 version allows me to choose other colors if desired)

Select cells with the error mark and a small dropdown menu will appear. Click it and choose Convert to Number.

Note this applies to regular formatting. Numbers changed by the TEXT function are not recognized as errors so you will probably be better off using the VALUE function instead.  
Posted by andrewe at 04:28

June 30, 2004

Those Pesky Zeros Again

For the last couple of nights I've been lurking around a certain Excel forum, part trying to help, part trying to learn, and part trying not to make too big a fool of myself (one can only wish).

One thing I noticed were a couple of questions about how to make zeros reappear. (Please see here for one way to make them disappear)

Let's say you have a part number that starts with a zero or two or three. With a General format, those zeroes just keep disappearing (the ones that the part number starts with, not ones entered later) Here are some ways to make them come back to life, all shiny and new.

Make Them Text
Just enter an apostrophe in front like this '012345 and you will get 012345. Very simple, very quick.

If you have lots of numbers to enter, right-click all cells that contain or will contain the zeroes, to select the Format Cells dialog box. Then choose the Number tab and push Text, OK. Finished.

One possible problem in both the above solutions is that these part numbers are now text. Although they still look like numbers, they are no longer recognized and are ignored by numeric functions.

Custom Formatting
Here's another way. The part number 012345 has six digits. Using the Format Cells dialog box, this time select Custom on the Number tab and enter six zeroes, 000000. This will force the the number to always appear as six digits regardless if they start with a zero or not. A bonus is that they are still numbers and can be calculated as such.

Last one. One method I thought up (though I'm quite sure others have preceded me) is to merely add the required number of zeros in front, enclosed in quote marks as in "000", and then add a zero behind to make it a number.

This is not a bad format. You can always have the same number of zeros in front, it adapts to any number of digits (although a fixed number digits as above may be what you are after), and still remains a calculable number. You can also do things like "000"0.00 for 2 decimals or "000"#,### (Be aware that the zeroes in front are now treated as text and not affected by the numerical formatting, so commas will not appear in their case)

Hopefully this picture will explain.

Posted by andrewe at 01:46

June 26, 2004

More on Comments

Here are some more comment tips that I think are quite handy. (I found them on the Contextures site run by Debra Dalgleish)

Changing a comment's shape
Right-click the comment's cell and select Edit Comment. Click on the border of the comment and the white pull tabs should appear like this.

On the Drawing toolbar, click the Draw button and then Change AutoShape. (The Draw button should be on the bottom left if Drawing toolbar is displayed, otherwise right-click any toolbar to select it)

You can also do things like change colors, font or shadow, either right-clicking the comment and selecting the Format Comment dialog box or pushing the appropriate buttons on toolbars.

Here's a picture of me selecting No Shadow on the Drawing toolbar with the Shadow Style button.

Drag the handles to re-size when you're finished. Here's the finished product.

Inserting a Picture
Add a bit of novelty by using a picture in a comment by using the Format Comment dialog box. Right-Click the comment to select it.

Select the Colors and Shapes tab, then push the Color dropdown arrow to find Fill Effects. Use the Picture tab to find the Select Picture button which enable you to browse your files and choose a suitable picture.

Posted by andrewe at 20:49

June 23, 2004

No Comment? Yes Comment!

Comments in real life may not always please, but in Excel they are usually a good thing. They remind and they instruct.

Apart from the usual Insert Comment, here are a couple of other ways to make comments work to your advantage.

Input Message using Data Validation
Select Data, Validation from the top menu and then to the tab shown as Input Message. (make sure Allow: Any value is selected on the Settings tab)

Write whatever you like for the Title and Input Message. (There seems to be a limit of 32 characters for the Title, but no limit for the Input Message)

Once completed, select any cell you entered the above Data Validation and watch your message appear (the message can also be dragged to wherever you like)

It's a good way to tell another user what should be entered in a cell without them having to guess.

Enter a comment into a formula
How about this? You can enter a formula using the N function (I'm not sure that's what it's really called, I think it stands for Numeric)

Let's say I want a comment or reminder to cell D6 where I already used the SUM formula to add cells D2:D5. As you can see, I've used the N function to add a comment at the end of the formula.

Beware it's limitations. It does not work with certain functions such as used in logic formulas (Refer to the Help section for more detailed information)

As an added tip, do long and complex formulas give you a headache? Why not add some spaces between the individual functions to make them easier to read?

This would be a good time to write a comment!  
Posted by andrewe at 23:23

June 01, 2004

Color Me Whatever

A very simple tip today.

If you are not satisfied with the colors available with Fill Color, Line Color or Font Color, you can change them by navigating to the Options dialog box in the Tools Menu.

Select the Color tab, then choose any color you want to modify (Don't choose a color that you will need later on) Push the Modify button and you will see a range of 127 colors and 15 shades of grey on the Standard tab.

Pick the one you want. The color you orignally selected will now be replaced with the new color. Keep in mind that this change will be limited to your current Workbook. Any changes you make can also be undone using the Reset button, this will revert all colors throughout the Workbook.

If this many colors are still not enough, you can select the Custom tab and make your own. Good luck!  
Posted by andrewe at 16:27

May 27, 2004


None other than Debra Dalgleish, MVP of Contextures was kind enough to point out some confusion regarding a previous post "Now You See it, Now You Don't" on May 13.

The point of the post was to illustrate how an area of a Worksheet can be used to have formulas in the cells themselves, with a clear AutoShape above to make a message appear using Conditional Formatting.

The post was confusing because I used a date related formula in a cell beneath and another date related formula in the Conditional Formatting. Let me say that there is no connection between the two formulas. What is more important is that the clear AutoShape to display the message at the right time, and the cell formula to be hidden using regular formatting (white Font Color)and Conditional Formatting (changing to black Font Color). The text on the clear AutoShape itself is white.

When the Conditional Formatting is activated, the cell font color (and cell background) will turn black to make the white text in the clear AutoShape appear like this.

Still confused? Send me an email at the link at the top right (under my handsome photo) and I'll do my best to explain.

Please feel free to contact me whenever you like. I'll try to help with any Excel related inquires (or direct you to people who can) and like I mentioned, feedback is always welcome!

Thanks again Debra!  
Posted by andrewe at 16:51

May 15, 2004

Error! Part 2

Conditional Formatting. It can be used for many things. Let's have a look at how we can use it for error spotting.

In a certain range of cells, all numbers entered must be greater than or equal to 0 and smaller than or equal to 10. One possible formula for the Conditional Formatting in cell B1 for example is =OR(B1<0,B1>10)

Hmmm, let's make it a little more interesting and say that all entries must be multiples of 0.5. Here's my modified formula =OR(B1<0,B1>10,ROUND((B1/0.5),0)<>B1/0.5)

There you have it. Any cell that does not fit all of the above criteria will be highlighted like this.

Have a nice weekend.  
Posted by andrewe at 04:18

May 14, 2004

Error! Part 1

There are many ways to show a user that he or she has entered an incorrect value into a cell.

I like to show an example using Data Validation. Let's assume that a person's date of birth must be entered into cell A1 as an entry requirement for a club where membership is restricted to people 20 years old or more.

Here's one possible formula. (I added the ISNUMBER function just in case someone enters something besides a date. Don't forget that dates in Excel are just numbers formatted in a special way)


Open the Validation dialog box from the Data menu at the top and select Custom for the Validation criteria. Then enter the above formula into the text box provided.

Next, select the Error Alert tab at the top and enter your custom Title and Error message.

Here what appears if you make an error.

Although my Error message doesn't really apply if someone has done something like entered text instead of a number, it should still get the point across.  
Posted by andrewe at 18:29

April 25, 2004

More Custom Formatting

I could probably write a book on Custom Formatting and still not cover everything :)

I see that the options offered on my Excel 2003 are becoming more diverse but here are some simple formats that you should know - adapt them to what best suits you. Right click the cell(s) you want to change and select the Format Cells dialog box, then choose Number, Custom.

Numbers & Currency
0 a general number format
0.00 a number with 2 decimal places (add or delete zeros for more or less decimal places)
$0.00 dollar currency with 2 decimal places
#,##0 a number with commas added for every thousand, million, billion...
$#,##0.00 dollar currency with 2 decimal places and commas added for every thousand, million, billion...
#,##0, will round your number to thousands
#,##0,, will round your number to millions

# ?/? simple fraction rounded up to 1 denominator
# ?/?? simple fraction rounded up to 2 denominators
# ?/??? simple fraction rounded up to 3 denominators
# ?/???? simple fraction rounded up to 4 denominators (This appears to be the limit for fractions, I don't know the limit for decimals but I can tell you it's a lot!)
# #/4 fraction divided into quarters
# #/16 fraction divided into 16ths
# #/100 fraction divided into 100ths

Dates and Time
Dates (see my previous post)
h:mm:ss hours, minutes and seconds
h:mm AM/PM hours and minutes with AM or PM

% Just add a percentage mark to a number format (decimal numbers or commas are okay)

Very useful. You can use this to enter almost anything and what you enter will be what is displayed. Note: You'll have to change to another format in many cases such as entering formulas otherwise they will not work.

@ You can use this to add whatever word you enter to text that is already entered into your format. For example, "This is a "@ will give you 'This is a boat', if you enter 'boat' into the cell. You can enter text before and after the @ mark. (Use double quotation marks for formatting, single quotation marks are just to show my examples)

Colors and Equations
You can do simple conditional formatting by entering colors or equations into square brackets. For example, [Red][=<100] will turn the font red for any number less than or equal to 100, but might cause the value to disappear if over 100. I suggest using a semicolon and using a formula such as [Red][=<100];[Black]. Combinations are also possible, such as [Red][=<100];[Blue][=>200] which means red font for any number equal or less than 100 and blue font for any number equal to or more than 200. (You might find that Excel adds it's General format as an automatic anti-error feature, this usually works to your advantage)

Okay, that's it for now. There is another simple way to make custom formats, I'll cover this sometime in the future.  
Posted by andrewe at 18:07

April 24, 2004

Date Clock

This is something I like to have on my files at work. I call it a Date Clock. It doesn't tell the time, just the date, but it's good to have if you can't remember what day or date it is and want to know in a hurry.

In the cell of your choice enter =TODAY(). You can also use =NOW().

This will give today's date (if you use =NOW(), it will also give the time that you open the file, enter the formula for the first time or it is re-calculated. Not so useful if you are just making a date clock).

The date by itself may be useful, but you can improve on it a number of ways. For example, you can right click the cell and select the Format Cells dialog box. Choose the Number tag and then Custom.

Depending on the format that already exists, you might see something like yyyy/mm/dd. You might also guess that 'y' means year, 'm' means month and 'd' means day. You can make your own custom format such as 'd mmmm, yyyy (ddd)'. Note: Don't enter the quotation marks and period in my example, the brackets are optional.

One 'd' or two 'd's will give you the day of the month, three 'd's or four 'd's will give you the weekday. One to four 'm's will give you the month in either numerical or text format, and any number of 'y's will give the year. I notice in my version of Excel that if I enter just one 'y', it automatically enters two 'y's, and if I enter three 'y's it automatically enters four 'y's. Same situation for more than four 'd's, 'm's or 'y's. It seems to be an automatic correction feature.

Anyway, the above 'd mmmm, yyyy (ddd)' custom format should give you 24 April, 2004 (Sat), at least for today. You can also add hyphens, slashes, brackets and spaces as well as text (use commas such as "Today is "). Have fun and experiment, I'll cover other custom format features sometime in the near future.

One last thing, I've noticed one change with later versions of Excel. Depending on the language your system is, formatting will vary accordingly. I have Excel in Japanese so I still get the same results, but not in English which was the default in earlier versions of Excel. You should be able to get around this by selecting the Date format first, then Custom format and adjusting to suit. (To get 24 April, 2004 (Fri) in Excel 2003, Japanese version I now have to enter [$-409]d mmmm, yyyy (ddd);@ to get it in English format because the Japanese format is now default. Of course this is a big improvement for people to have automatic formatting in the same way that they would normally write it. Like I say, experiment and have fun. Excel can be very adaptable.  
Posted by andrewe at 23:09