Conditional Formatting in ref to active cell

[KUMPFfrog]

I would like to apply conditional formatting in column B to highlight the
cell in the same row as the active/selected cell. Is there a way to
reference the active cell in a function/formula? I'm using 2003 pro.

 

[Tom Hutchins]

This doesn't use conditional formatting, but achieves the same effect.

Pthis code in the code page for the sheet where you want this to happen:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Range("B1").EntireColumn.Interior.ColorIndex = xlColorIndexNone
ActiveSheet.Range("B" & Target.Row).Interior.ColorIndex = 36
End Sub

If you are new to macros, david mcritchie has some instructions on his site
for navigating the vba editor and how to copy/paste macros into your project.

http://www.mvps.org/dmcritchie/excel/excel.htm

Hope this helps,

Hutch

 

[Tom Hutchins]

Here is another way that does use conditional formatting. In a VBA module in
your workbook, paste this user-defined function:

Public Function CurrRow() As Long
CurrRow = ActiveCell.Row
End Function

In the ThisWorkbook module of the workbook, paste this event code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Calculate
End Sub

On the worksheet where you want to apply the condition formatting, select
column B, then select Conditional Formatting from the Format menu. Change
'Cell Value Is' to 'Formula Is' and enter the following formula:

=(ROW(B1)=CurrRow())

Then click the Format button to select however you want the column B cells
to be highlighted. Click OK to close the Format dialog, then OK again to
close the Conditional Formatting dialog.

Hope this helps,

Hutch