More On Automatic Highlighting

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

"Earlier this week I was experimenting again with highlighting using the CELL function.

'Highlight the Active Cell
The syntax of the CELL function is CELL(info_type, [reference]), the "reference" part meaning a cell such as A1 etc.

'When the reference is omitted, the CELL function refers to the active cell. This can be readily used to highlight with some Conditional Formatting and Screen Updating.

'First navigate to the Visual Basic Editor (push Alt + F11), locate your file, then select Microsoft Excel Objects, ThisWorkbook. At the top, you will see 2 dropdown lists. Use the one on the left to select Workbook, then the one on the right to choose SheetSelectionChange. Add "Application.ScreenUpdating = True" like this.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

'Go back to Excel, select your highlight range, then from the top menu choose Format, Conditional Formatting, select Formula Is from the dropdown list and enter this formula.

=CELL("address")=ADDRESS(ROW(),COLUMN())

'Push the Format button, make a color selection from the Patterns tab, then push OK twice.

'You should get something like this.



'The address of the active cell (using CELL) equals that of the address obtained with the ROW and COLUMN functions. TRUE!

'Note: You can use Worksheet_SelectionChange instead if you want to work with just one particular sheet, enter the code in the appropriate sheet instead of This Workbook.

'Highlight the Row and Column
Last time I mentioned highlighting both the row and column like this.

=OR(CELL("row")=ROW(), CELL("col")=COLUMN())

'This results in a cross shape where the row and column overlap. I didn't feel this was the best as a visual aid (the whole purpose of the exercise) so after some experimentation, I came to the conclusion that a reverse "L" was the best (same as seen on Ivan Moala's site, The Xcel Files), in that it seems a lot easier to work with.

'Here is the appropriate conditional format (use the same steps as above)

=OR(AND(CELL("row")=ROW(),CELL("col")+1>COLUMN()),AND(CELL("col")=COLUMN(),CELL("row")+1>ROW()))

While I mention it, Ivan told me of his Hex File Reader that contains an example of the code he uses. You can study his code and gain a valuable download at the same time, far too good an opprtunity to miss ;-)"