Excel

Automatic Highlighting Using Autoshapes

Ease of Use

Easy

Version tested with

97, 2003 

Submitted by:

Andrew

Description:

An alternative to Automatic Highlighting with Conditional Formatting. 

Discussion:

Takes advantage of transparent Autoshapes. Can also be used together with my Conditional Formatting method. This method also allows you make to make "bookmarks". 

Code:

instructions for use 

			
Option Explicit 
 
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("B3") 
    Set myBottomRightCell = Range("H17") 
     ' ****************************************************************
     
    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

How to use:

  1. Open the Visual Basic Editor (Push Alt + F11 simultaneously).
  2. Paste the above code in the appropriate Sheet Module.
  3. Set the Top Left Cell and Bottom Right Cell of the range as indicated in the code comments.
  4. To use with an entire Workbook, replace the top line with "Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)" without quote marks.
  5. In the above code, the subroutine is exited if the Forms Toolbar Checkbox linked to Cell A1 shown in the sample file is turned off. This is so you make any major edits to your workbook without interference to Undo (normally this type of code will have this effect). Once editing is finished, turn the Checkbox back on, and highlight ling will begin again. (Note: This is optional, if you are comfortable without Undo, remove the code line "If Range("A1") = False Then Exit Sub ' Optional")
  6. To set up the Forms Toolbar Checkbox - If the Forms Toolbar is not visible, right click any visible Toolbar, you will see a list of Toolbars and their status. Check the Forms Toolbar to make it visible. Then push the Checkbox button on the Toolbar and place the Checkbox at the top of the Worksheet (or anywhere where it is handy).
  7. Remove the text from the Checkbox (eg "Checkbox1") if desired, then right click the Checkbox and select the menu option at the bottom, then Linked Cell on the Controls tab and select Cell $A$1. Then go to Cell A1 and change the font color to White (or the same color as the cell background) to hide TRUE/FALSE which becomes the cell's value when the Checkbox is checked/unchecked.
  8. If you don't wish to use Cell A1, change the code "Range("A1")" at the top to suit.
  9. Further information regarding using the Highlight Shape is provided with the sample Workbook.
 

Test the code:

 

Sample File:

Automatic_Highlighting_Shapes.zip 11.2KB 

Approved by mdmackillop


This entry has been viewed 227 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express