# Highlight Active row



## ExcelChampion (Feb 15, 2007)

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 (Feb 15, 2007)

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 (Feb 15, 2007)

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 (Feb 16, 2007)

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 (Feb 18, 2007)

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 (Feb 18, 2007)

> 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 (Feb 18, 2007)

> 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 (Feb 18, 2007)

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 (Feb 19, 2007)

> 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 (Feb 19, 2007)

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.


----------



## ExcelChampion (Feb 19, 2007)

With the above mentioned CF process, you can add a checkbox to toggle on/off no problem...so that's not an issue.

I feel reluctant to ask users to download add-ins, let alone asking them to "use" them.  Using one or two CF slots sure is a drawback, but it is certainly not the end of the world.  I think you just need to take it project by project...not all projects use even one CF slot...others you have to resort to VBA.  I will probably download and use the link you mentioned, but I doubt I'd sent it to other users and tell them, "If you want your workbook to work correctly then you need to download this or that...".  The ONLY time I'd do that is if they want to convert XLS to PDF...in which case I'll have them download the free PDF Creator...but I'd always clear with them first.


----------



## Greg Truby (Feb 19, 2007)

> ...I feel reluctant to ask users to download add-ins, let alone asking them to "use" them.  Using one or two CF slots sure is a drawback, but it is certainly not the end of the world.  I think you just need to take it project by project...not all projects use even one CF slot...others you have to resort to VBA.  I will probably download and use the link you mentioned, but I doubt I'd sent it to other users and tell them, "If you want your workbook to work correctly then you need to download this or that...".  The ONLY time I'd do that is if they want to convert XLS to PDF...
> ~ExcelChampion



 Okay... so when a co-worker sees that I have the ability to drop a crosshairs onto a worksheet when we're over at my desk looking at a big workbook and she says "oh, cool, how do you do that?!  I'd love to be able to do that on several spreadsheets that I get from our key accounts."  Yer a thinkin' I oughter say "well, you could just hop out to the internet & download this add-in and voilá.  But I wouldn't recommend _that._  You're much better off learning how to program all sorts of fancy conditional formatting hocus pocus and go in and add all sorts of code to each account's workbook as it comes in each month."?  Okey-dokey... maybe I'm just a sadist... but I'm just gonna continue to recommend the add-in thing to my co-workers and not try to go the "easy" route of recommending they become skilled VBA programmers... :wink:


----------



## Richard Schollar (Feb 19, 2007)

> ... maybe I'm just a sadist...  :wink:



No comment, Big Guy!!! Congrats on the 6,000th post by the way!!!! And prolly only 1,000 or so of those spent on topic misdirection too


----------



## ExcelChampion (Feb 19, 2007)

I wasn't referring to recommendations.  I was referring to building applications for others.  I do not feel comfortable building apps that require the user to download an add-in.  In your scenario above, I would certainly do the same thing.  But if I'm building something that may be shared or used on several different PCs, and I had to make sure that each PC had the add-in AND that each user knew how to load and use the add-in...I think I'll pass.  Better to just build your apps with native Excel functions.


----------



## Greg Truby (Feb 19, 2007)

> ...I do not feel comfortable building apps that require the user to download an add-in....



Agreed.  I shied away from using formulae that took advantage of anything in _MoreFunc_ for years for the same reason.  I understand that one can now include MoreFunc inside the project so it does not have to be downloaded as a separate add-in; though I confess, I've not yet needed to cross that bridge.


----------



## ExcelChampion (Feb 19, 2007)

> Agreed.



Really?  You're going to let me off that easy?  I guess there's a first time for everything.


----------



## Greg Truby (Feb 19, 2007)

> ... You're going to let me off that easy? ...



Must be my giddiness at it bein' Presidents' Day 'n' all.


----------



## JGraves (Jan 11, 2008)

Chip's Rowliner disables the undo feature.  Not good.  

I wished there was a way to highlight the active cell and not disable things line undo, copy paste, or conditional formatting.  I would like this to be from the personal vba file so it will work with any work sheets I amy open.

I have tried many different codes from here and I can  not get them to work.


----------



## Greg Truby (Jan 11, 2008)

JGraves said:


> Chip's Rowliner disables the undo feature. Not good.


Chip is a *******jack programmer of the first order. If there was a way to keep from clearing the Undo stack, believe, Chip would know it. Almost any macro that does almost anything will clear the Undo stack. The problem is things you do via VBA do not ADD to the Undo stack. So Excel cannot UNDO anymore - to do so would wreak untold havoc.



JGraves said:


> I wished there was a way to highlight the active cell and not disable things line undo, copy paste, or conditional formatting.


 You can still copy and paste and apply conditional formats with Chip's add-in. That's one of the reasons why I like it.


----------



## JGraves (Jan 11, 2008)

Thanks for your quick response.

I have just started a new position with  my company and I am doing lots of things with excel that I have not done before.  

I'm trying to find some things to help me out.  Unfortunatly I need to be able to use the undo function so I guess I am out of luck.


----------



## Oorang (Jan 11, 2008)

Greg Truby said:


> Chip is a *******jack programmer of the first order. If there was a way to keep from clearing the Undo stack, believe, Chip would know it. Almost any macro that does almost anything will clear the Undo stack. The problem is things you do via VBA do not ADD to the Undo stack. So Excel cannot UNDO anymore - to do so would wreak untold havoc.



I don't know why everyone (even very experienced people like yourself Greg) seems to take that line. You can very set your macros up to be undone. Take a look at the OnUndo property. I have been making my xla actions undoable for a while now. 

http://msdn2.microsoft.com/en-us/library/aa195810(office.11).aspx
http://j-walk.com/ss/excel/tips/tip23.htm


----------



## Greg Truby (Jan 11, 2008)

Oorang said:


> I don't know why everyone (even very experienced people like yourself Greg) seems to take that line. You can very set your macros up to be undone. Take a look at the OnUndo property. I have been making my xla actions undoable for a while now. In fact CP has a whole article about it... So I think he knows too. They are just a pain to set up, so he likely skipped it.


 
Aaron,

Quit skimmin'! Didn't say you cannot create your own UNDO. I have done that myself. But even if you do create your own UNDO, it does not retain the Undo stack. Unless I am mistaken (and I would be thrilled to be wrong), there is now way to append to the existing call stack. After you call App.OnUndo the undo stack is one action deep - that which you specified.  Even if the undo stack was fifty deep before you called your macro and your macro only does one wee thing to a cell, the undo stack gets cleared (whether or not you call OnUndo).


----------



## Oorang (Jan 11, 2008)

Just So


----------



## Oorang (Jan 11, 2008)

You know Greg, now that someone has said it can't be done this is driving me crazy. Because I am sure that info is stored somewhere. I have used filemon and regmon to see if I could be so lucky but I am pretty sure the undo info is being stored in memory. If we could figure where, the undo stack doesn't get cleared until a macro changes the workbook. So you could copy the undo stack out of memory before any workbook changing action, then at the end, restore it.


Edit: Not a 100% positive it's not being stored in a tempfile have to double check here.


----------



## Greg Truby (Jan 11, 2008)

If you figure it out, let us know!  Obviously it is being stored somewhere.  I know that if you bump the registry setting to increase the size of the undo stack it does take more memory.  

But I have to think that it would be pretty danged hard to lay your hands on it because in all honesty you would have to be one heck of a good programmer to properly write your OnUndo to completely roll back every change you've done if your macro did anything more than some pretty basic stuff.


----------



## Oorang (Jan 11, 2008)

I agree, it's doability would very much depend on how they plugged it in. If it's something simple you could just copy the values stored. Let them be cleared, then put the values back along with a reference calling your undo macro.
WinHex allows you to monitor what excel is doing to memory, so I'd imagine with time it could be figured out what and where it's storing it. I'm sure the offset location (or formula to derive the offset) would be static. The question then is figuring out how get the starting point the OS assigns it.


----------



## Oorang (Jan 11, 2008)

OK, I can confirm it is definitely in the memory but the offset is moving around. It actually stores the Undo Text and Function call in plain text. So you search for a garbage sequence until you find one not in memory then use that as the Undo Text and viola, the location. 
So now that we know it's there it's a matter of figuring out the memory layout. With the offset moving around I thing it's going to be a little less trivial.


----------



## erik.van.geit (Jan 11, 2008)

Hi, guys,

Interesting discussion 
About UNDO: it's true that running VBA, will disable Undo, but there are some tricks...
Example: You can make the current time running in a cell (for example, because I wouldn't do that in a project), while still having undo available. Time display stops when you are editing a cell, but afterwards again showing correct time.
Challenge: what's the trick? Very easy in fact. *

Do not forget, this is The Lounge 

have a nice weekend,
Erik

* solution: http://www.mrexcel.com/forum/showthread.php?t=238323


----------



## Oorang (Jan 11, 2008)

Hi Erik 
I'm not sure what you suggesting. Any change to a worksheet by a macro (called by OnTime or not) clears the undo stack. So even if you call the macro by OnTime, the undo stack will be cleared. 
Or did I just completely fail to get what you were saying?

Oh, and Greg.... They make you live in a field?


----------



## Oorang (Jan 11, 2008)

Hey Erik,
Do you still have that bloody nose API joke code?


----------



## erik.van.geit (Jan 12, 2008)

Oorang said:


> Hi Erik
> I'm not sure what you suggesting. Any change to a worksheet by a macro (called by OnTime or not) clears the undo stack. So even if you call the macro by OnTime, the undo stack will be cleared.
> Or did I just completely fail to get what you were saying?


As you know I'm not a "real programmer". Consequently this involves some limitations but also advantages. I will try out things which a programmer wouldn't, because he thinks it's not possible.
Perhaps my explanation in the other post was to short.


> *if you absolutely want to use a cell, whithout the drawback, there is a trick: use running-clock on other (hidden) sheet and refer to there with a formula


If you change sheet1 by code, you will still be able to have undo on sheet2. It is not OnTime specific.

Put =Sheet1!A1 in some cell on sheet2.
Change something on sheet2.
Run this code (even mutiple times)

```
Sub test()
    With Sheets("Sheet1").Range("A1")
    .Value = .Value & "&"
    End With
End Sub
```
You will see that "&" is added on sheet1, reflected on sheet2, because of the formula. But you have still undo available!!

You have still Undi available, when you run

```
With Range("A1")
    .Value = .Value & "&"
    End With
```
while sheet1 is active.

If you want, throw me an email. "time running in cell" I'll send you an example.

best regards,
Erik


----------



## erik.van.geit (Jan 12, 2008)

Oorang said:


> Hey Erik,
> Do you still have that bloody nose API joke code?


search for "bleeding nose"
http://www.mrexcel.com/forum/showthread.php?t=231988&highlight=bleeding+nose


----------



## Greg Truby (Jan 13, 2008)

Oorang said:


> Oh, and Greg.... They make you live in a field?


 
Nope. You're a few minutes east and south.


----------



## Oorang (Jan 14, 2008)

rofl When I posted that I hadn't figured out you had to remove the N & W  So I just went to the coordinates. Hadn't actually realized that it would mean almost 60 miles 


Greg Truby said:


> Nope. You're a few minutes east and south.


----------



## ExcelChampion (Jan 14, 2008)

What Erik is saying is that it is not always true that running a macro will clear the stack.  There are some things a macro can do that won't clear the stack.  But, that is beside the point.  The point here is how do you save the stack.


----------

