|
|
|
|
|
|
|
|
|
Excel
|
Automatic Highlighting Using Conditional Formatting
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
97, 2003
|
|
Submitted by:
|
Andrew
|
|
Description:
|
An easy way to implement Automatic Highlighting.
|
|
Discussion:
|
Take advantage of the CELL function - if no range is specified in the formula, it refers to the active cell. ScreenUpdating updates the highlighted cells when a new selection is made.
|
|
Code:
|
instructions for use
|
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub
|
|
How to use:
|
- Open the Visual Basic Editor (push Alt + F11 simultaneously).
- Paste the above code in ThisWorkbook.
- To use with single sheets only, replace the top line with "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" (without quote marks)
- Return to Excel and select your range.
- Go to Format, Conditional Formatting on the top menu.
- Select Formula Is and enter one or more of the formats shown below and in the attached file.
- Push the Format button and choose the Font, Borders, Pattern format or combination of your choice, then push OK.
|
|
Test the code:
|
- Formula Is
-
- Highlight Active Cell
- =CELL("address")=ADDRESS(ROW(),COLUMN())
-
- Highlight Active Row
- =CELL("row")=ROW()
-
- Highlight row and column
- =OR(AND(CELL("row")=ROW(),CELL("col")+1>COLUMN()),AND(CELL("col")=COLUMN(),CELL("row")+1>ROW()))
-
- Hybrid - Active cell with row and column
- =CELL("address")=ADDRESS(ROW(),COLUMN())
- =OR(AND(CELL("row")=ROW(),CELL("col")+1>COLUMN()),AND(CELL("col")=COLUMN(),CELL("row")+1>ROW()))
|
|
Sample File:
|
Automatic_Highlighting.zip 10.07KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 337 times.
|
|
|