New Cell Spotter

http://blog.livedoor.jp/andrewe/archives/cat_415504.html

Today I modified the code for Cell Spotter.



As you can see some extra items have been added to the menu. In addition to the lines that appear when you double-click cells, you can also use "Highlight" to make the same type of lines appear around multiple selections - the cells are determined by selecting them with the mouse. Unlike the regular Cell Spotter lines, these lines don't disappear automatically unless you choose to delete them.



Also, the line color(s) and thickness can be altered by changing "Settings".

And finally, you can choose to turn Cell Spotter on or off as convenient. Any changes made (including "Settings") are recorded in the registry.

A little early for Christmas but I hope you like it. The link is here.

Visibility In Excel Part 3 User Friendly Rows

http://blog.livedoor.jp/andrewe/archives/cat_415504.html

Excel is vertical-centric (Okay, I made this word up)

Don't believe me? The user interface is mostly at the top or bottom - Main menu at the top, Autofilter at the top, Name Box and Formula Bar at the top, Status Bar at the bottom, Sheet tabs at the bottom...why is it so? I guess the reason is that it is a lot easier to work "vertically" than "horizontally", even mouse wheels are designed to work this way.

Which leads to a problem with rows. Looking at several rows with data, it's easy to lose track of which row you are supposed to be looking at. So here are a few tips to make life easier.

1. Increase the row height
If this does not interfere with how your files are set up, changing row heights to somewhere between 18 and 24 points will help reduce eyestrain.

Before (14.25 points)


After (18.00 points)


Easier to see with just a minor change in row height.

2. Row Shading
Changing the color of every other row helps too. You can just change the Fill color, or use Conditional Formatting. (This has the advantage of chaning the row color if you decide to insert more rows later on)

Here's a link to one of my old posts.

My preferred formats are

=ODD(ROW())=ROW()

or

=EVEN(ROW())=ROW()

mainly because they are simple to remember...

The difference?



Getting better again.

3. Automatic row highlight
This is some code I wrote a week or so ago. Place it in the appropiate sheet module and change the top left cell and bottom right cell of your range to suit where indicated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1") = False Then Exit Sub ' Optional
On Error Resume Next
Dim myRange As Range
Dim myTopLeftCell As Range
Dim myBottomRightCell As Range

' Set your Top Left Cell and Bottom Right Cell, (Range Names can be used also)
' ****************************************************************
Set myTopLeftCell = Range("B2")
Set myBottomRightCell = Range("H20")
' ****************************************************************

If Target.Row >= myTopLeftCell.Row And _
Target.Offset(Selection.Rows.Count - 1).Row <= myBottomRightCell.Row And _ Target.Column >= myTopLeftCell.Column And _
Target.Offset(, Selection.Columns.Count - 1).Column <= myBottomRightCell.Column Then Set myRange = Selection If ActiveSheet.Shapes("hSelection") Is Nothing Then ActiveSheet.Shapes.AddShape(msoShapeRectangle, myTopLeftCell.Left, Selection.Top, _ myBottomRightCell.Offset(, 1).Left - _ myTopLeftCell.Left, Selection.Height).Select With Selection With .ShapeRange .Fill.Visible = msoFalse .Line.ForeColor.SchemeColor = 12 ' Change Color Here .Line.Weight = 2.25 ' Change Line Weight (Thickness) Here .ZOrder msoSendToBack .Shadow.Visible = msoFalse End With .Name = "hSelection" .PrintObject = False End With Else With ActiveSheet.Shapes("hSelection") .Left = myTopLeftCell.Left .Top = Selection.Top .Width = myBottomRightCell.Offset(, 1).Left - myTopLeftCell.Left .Height = Selection.Height .ShapeRange.Shadow.Visible = msoFalse End With End If myRange.Select End If Set myTopLeftCell = Nothing Set myBottomRightCell = Nothing Set myRange = Nothing End Sub

Like my previous Data Validation code, it assumes that there is a Forms Toolbar checkbox in Cell A1 (Turn it the checkbox on and off to stop the code from running, it tends to interfere with Undo)

Here's some code to quickly insert a Checkbox.

Sub AddCheckbox()
On Error Resume Next
ActiveSheet.CheckBoxes.Add(0, 0, 0, 0).Select
With Selection
.Characters.Text = ""
.Value = xlOn
.LinkedCell = "A1"
End With
Range("A1").Select
Selection.Font.ColorIndex = 2
End Sub

With the Checkbox turned on, you are ready to go. Select any cell within the range and the row is highlighted accordingly.

Here's a pic.



I've also been working on a new version of my Cell Spotter to work in a similar way. Just now I'm testing it at work, I'll upload it when it's ready :-)