Highlight Active row

[ExcelChampion]

I was asked to implement this code, which highlights the activerow (I don't know where it came from so I can't give credit):-

Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
    Dim InterSectRange As Range
    If ActiveSheet.Name = "Sum" Then
    Set InterSectRange = Application.Intersect(Range1, Range2)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
    Else
    End If
End Function

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    If InRange(ActiveCell, Range("Database")) Then
        Application.ScreenUpdating = False
        ActiveSheet.Unprotect "password"
        ThisWorkbook.Unprotect "password"
        Const cnNUMCOLS As Long = 13
        Const cnHIGHLIGHTCOLOR As Long = 36  'default lt. yellow
        Static rOld As Range
        Static nColorIndices(1 To cnNUMCOLS) As Long
        Dim i As Long
        If Not rOld Is Nothing Then 'Restore color indices
            With rOld.Cells
                If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore
                For i = 1 To cnNUMCOLS
                    .Item(i).Interior.ColorIndex = nColorIndices(i)
                   Next i
            End With
        End If
        Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS)
        With rOld
            For i = 1 To cnNUMCOLS
                nColorIndices(i) = .Item(i).Interior.ColorIndex
            Next i
            .Interior.ColorIndex = cnHIGHLIGHTCOLOR
        End With
    Else
    End If
    Application.ScreenUpdating = True
    ActiveSheet.Protect "password"
    ThisWorkbook.Protect "password"
End Sub

Problem is, if you Insert a row the code fails to highlight anything. Secondly, trying to insert a row with a macro fails miserably. I tried like heck to edit the code so that it would work, but it either interfered with my macro or just failed miserably any time I made a small change to it.

So, I scrapped it all together and thought I'd share what I did, since it seems so simple, yet elegant (forgive me if everyone already knew of this).

1. Go to Insert/Name/Define. For the Name, I used, "myCellFormat". For the Reference I used: =Get.Cell(2)=Row()

2. Now apply Conditional Format:- Select a range and go to Format/Conditional formatting. Choose "Formula is" and use the formula, =myCellformat, and then select the Light Yellow color pattern and click Ok and then click Ok again.

3. The only thing left to do is make sure that it calculates. The formulas will only update when forced to calculate so I used a Worksheet_SelectionChange event to fire "Calculate" to force the calculation. After it calculates, the activerow will turn Light Yellow.

I think it's neat in it's simplicity.

 

[Oaktree]

Hi there.

Not to trivialize what you're done, but there's actually a hall of fame writeup for this by Ivan here that you might also consider:

http://www.mrexcel.com/board2/viewtopic.php?t=14546

 

[ExcelChampion]

Yes, I've seen and used that before. However, there are some drawbacks to using that code. I guess my point was the simplicity of the above mentioned method. The only code used is (besides the XLM 4.0 macro command in the Named reference),

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Calculate
End Sub

...along with a Named formula and conditional formatting. Again, I think it's neat in it's simplicity...not to mention that it seems to be robust.

 

[ExcelChampion]

As well, Ivan's process can be simplified by creating one more conditional format. So, the entire process would be to create two named references and two conditional formats, and use the snippet of code below.

First select your range (or select all of the cells)

then...

1. Insert/Name/Define:- myCellRow; =Get.Cell(2)=Row()

2. Insert/Name/Define:- myCellCol; =Get.Cell(3)=Column()

3. 1st Cond Format:- Format/Condition formatting:- Formula is; =myCellRow; Lt Yellow Pattern

4. 2nd Cond Format:- Format/Condition formatting:- Formula is; =myCellCol; Lt Yellow Pattern

5. In the worksheet module:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub

The only downfall to this is that two Conditional format slots end up being used. However, preserves current cell formatting.

Edit:- You can get a cross-hair effect if you use borders instead of the Lt Yellow pattern.

 

[Richard Schollar]

That's a nice & simple solution

Instead of forcing a sheet calculation (which may or may not have consequences depending on what's on the sheet) you can use:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

EDIT:Brian From Maui shows you can also do it without use of an XLM4 macro here:

http://www.mrexcel.com/board2/viewtopic.php?p=1081454#1081454

 

[mortgageman]

Hi there.

Not to trivialize what you're done, but there's actually a hall of fame writeup for this by Ivan here that you might also consider:

http://www.mrexcel.com/board2/viewtopic.php?t=14546

I took a look at your link. Acording to the write up, you cannot copy and past if you use this method. That seems like a major issue.

 

[ExcelChampion]

EDIT:Brian From Maui shows you can also do it without use of an XLM4 macro here:

http://www.mrexcel.com/board2/viewtopic.php?p=1081454#1081454

Very good! I especially like the use of screenupdating instead of calculate. Very good indeed.

 

[NateO]

Hello,

Acording to the write up, you cannot copy and past if you use this method. That seems like a major issue.
{snip}

Ivan has also provided an alternative method for working around this limitation, eh. See the following write-up:

http://xcelfiles.homestead.com/Excel02.html#anchor_67

I'm not sure if I'm all that crazy about this approach, but it was by request...

 

[Brian from Maui]

EDIT:Brian From Maui shows you can also do it without use of an XLM4 macro here:

http://www.mrexcel.com/board2/viewtopic.php?p=1081454#1081454

I got this from Yogi Anand!

 

[Greg Truby]

Am I like the only guy that prefers Chip's rowliner add-in? Even before I knew of Ivan's HOF entry, I wrote up code to do all of this on my own. It's a dandy learning experience. Wonderful thing to try your hand at if you want a great challenge as a learning exercise. But this whole approach rather stinks it up big time if you want something where you can apply the cross-hairs to any workbook any time, any where. You've got to worry about how many CF there already are or else go in and re-establish the original formatting...not to mention that you have to either add code to the workbook's project or else write your own add-in with a WITHEVENTS copy of the application and intercept and you're still monkeying with cell's formats - blech, yuck, patooey... When I saw Chip's solution of laying shapes over the top and where I can quickly toggle it on or off - manna from heaven, IMHO. to Chip for that one.