none
VBA - when in cell, highlight another?

    Question

  • Is there a way to do a temporary highlight on a cell when you enter another one?

    i.e., when user enters cell F2, say, the macro would then colour or somehow place a border or highlight cell B2.

    I've googled and searched for last couple of days but I must not be asking the right question as nothing pertinent comes up.

    Thanks.

    vendredi 2 mars 2012 22:28

Réponses

  • Put the following event macro in the worksheet code area:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim B2 As Range, F2 As Range
    Set B2 = Range("B2")
    Set F2 = Range("F2")
    If Intersect(Target, F2) Is Nothing Then
        B2.Interior.ColorIndex = xlNone
    Else
        B2.Interior.ColorIndex = 6
    End If
    End Sub


    Because it is worksheet code, it is very easy to install and automatic to use:

       1. right-click the tab name near the bottom of the Excel window
       2. select View Code - this brings up a VBE window
       3. paste the stuff in and close the VBE window

    If you have any concerns, first try it on a trial worksheet.

    If you save the workbook, the macro will be saved with it.


    To remove the macro:

       1. bring up the VBE windows as above
       2. clear the code out
       3. close the VBE window

    To learn more about macros in general, see:

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

    To learn more about Event Macros (worksheet code), see:

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


    gsnu201202

    samedi 3 mars 2012 00:42
    Modérateur
  • for the issue around nothiing happening it may be that the events are now switched off as you existed from the debug area

    use this code to ensure events are enabled:

    sub SetEventsOn()
    application.enableevents = true
    end sub

    Then I assume that you want to highlight the relative cell in col B when a cell in col F is selected - in which case

    Public LastSelectedRow As Long
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'check to see if there has been a previous selection in col F
    If LastSelectedRow = 0 Then LastSelectedRow = Target.Row
    Dim colOffset As Integer
    colOffset = -4
    Dim colCheck As String
    colCheck = "F"
    Dim iSect As Range
    Set iSect = Intersect(Target, Columns(colCheck))
    If Not iSect Is Nothing Then
    '1st remove the colour from the last selected row
        Cells(LastSelectedRow, colCheck).Offset(0, colOffset).Interior.ColorIndex = xlNone
        
        LastSelectedRow = Target.Row
        
    'now colour the cell you want
        Target.Offset(0, colOffset).Interior.ColorIndex = 6
    End If
    End Sub
    should work in compatability mode or not


    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    vendredi 9 mars 2012 02:53

Toutes les réponses

  • You may be interested in this:

    http://www.cpearson.com/excel/rowliner.htm

    vendredi 2 mars 2012 22:55
  • Put the following event macro in the worksheet code area:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim B2 As Range, F2 As Range
    Set B2 = Range("B2")
    Set F2 = Range("F2")
    If Intersect(Target, F2) Is Nothing Then
        B2.Interior.ColorIndex = xlNone
    Else
        B2.Interior.ColorIndex = 6
    End If
    End Sub


    Because it is worksheet code, it is very easy to install and automatic to use:

       1. right-click the tab name near the bottom of the Excel window
       2. select View Code - this brings up a VBE window
       3. paste the stuff in and close the VBE window

    If you have any concerns, first try it on a trial worksheet.

    If you save the workbook, the macro will be saved with it.


    To remove the macro:

       1. bring up the VBE windows as above
       2. clear the code out
       3. close the VBE window

    To learn more about macros in general, see:

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

    To learn more about Event Macros (worksheet code), see:

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


    gsnu201202

    samedi 3 mars 2012 00:42
    Modérateur
  • Hello, and thank you for the above!

    I tried it but unfortunately I was getting a debugging error.  That's when I realized to forgot to mention two things last night.  Sorry about that, I was doing this from work late last night and I was trying to get everything done before leaving for the weekend.

    First thing, at work I have Word 2007 but at home I have Word 2003 so the file is saved in compatibility mode to be able to use it.

    Secondly, there is conditional formatting in each cell as I have alternate row colouring going on.  I don't know if that would make a difference but didn't think to mention it last ngiht.

    I "installed" the code in the worksheet portion of the VBE.  At first I was getting a debug error code whenever I changed cells the first few times I moved around as I mentioned; now I'm not getting that error code but nothing happens when I move into and out of any cell in the F column (hopefully, code would highlight the cell on the corresponding row in column B when entering a cell in column F).

    Would there be any way to finetune this so it'll work?

    Thanks much!  :oD



    • Modifié SGFan samedi 3 mars 2012 13:52
    samedi 3 mars 2012 13:46
  • You may be interested in this:

    http://www.cpearson.com/excel/rowliner.htm

    Thank you.  I'm actually not too much of a fan of add-ins, though, simply because I do contract work and it got to be a pain to remember to "install" in each new place.  So I have tried since early on to make native changes to the workbooks themselves that'll work without any modications and without the user having to do anything other than open the workbook.

    Thanks for this though.  It looks very interesting and very good.  Something to keep in mind, for sure.

    Cheers!  :oD

    samedi 3 mars 2012 13:50
  • Hi SGFan,

    Welcome to the MSDN forum!

    How is it going with the problem currently?

    I think Gary's suggestion is nice and the code works fine on my computer with Excel 2010. If you want to enable macro in the workbook, you have to save the workbook as "Excel Macro-Enabled Workbook(*.xlsm)". 

    Please try a simple excel workbook with Gary's suggestion.

    Also, when you get the debug error, please show us the error message so that we can understand the issue better.

    Have a nice day!

    yoyo


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    mardi 6 mars 2012 06:41
    Modérateur
  • Hi SGFan,

    Welcome to the MSDN forum!

    How is it going with the problem currently?

    I think Gary's suggestion is nice and the code works fine on my computer with Excel 2010. If you want to enable macro in the workbook, you have to save the workbook as "Excel Macro-Enabled Workbook(*.xlsm)". 

    Please try a simple excel workbook with Gary's suggestion.

    Also, when you get the debug error, please show us the error message so that we can understand the issue better.

    Have a nice day!

    yoyo


    Yoyo Jiang[MSFT]

    Good Morning!  Thank you for your response!  Sorry that I missed giving out important information.  I thought I had but that was from another post elsewhere <g>.

    Although I'm working in Excel 2007, I need to access this in Excel 2003 so it's saved in compatibility mode.  Re the error, unfortunately, I got the error twice, but since then, when I click to go to another cell, no error comes up but nothing happens/changes, either so don't know what the actual error was as it hasn't come up again..

    Is there anything else I need to mention to aid you kind folks in helping me out re this?

    Thanks!  :)


    • Modifié SGFan mardi 6 mars 2012 16:03
    mardi 6 mars 2012 16:02
  • Hi SGFan,

    Welcome to the MSDN forum!

    Yoyo Jiang[MSFT]

    I see that I was remiss in not saying thank you, so, a belated THANK YOU for the welcome!!  :oD  I go back some time to the newsgroups so it just took me a while to find out where the MVPs went.   I'm sure they're all rolling their eyes now at this point <lol>.  But it's thanks to your folks efforts that we do as well as we do, no doubt about it.

    Thanks!  :)

    jeudi 8 mars 2012 18:48
  • Is there anything else that I'm missing?  When I initially put in this code, every time I then clicked out of a cell, I'd get that action where the vbe would open with a message to debug or not the code.  I'm sorry to say that after 2 or 3 times, this stopped happening and I didn't take note of the error.

    Now, however, when I click in and out of any cell, even the ones involved, absolutely no highlighting occurs or not.  It's as if nothing happens.

    I'm working in Excel 2007 but this workbook is saved in compability mode to an earlier version of Excel as it's one I use on different machines and have Excel 2007 on one and Excel 2003 on the other.

    Thanks for any help!  :oD

    jeudi 8 mars 2012 18:50
  • for the issue around nothiing happening it may be that the events are now switched off as you existed from the debug area

    use this code to ensure events are enabled:

    sub SetEventsOn()
    application.enableevents = true
    end sub

    Then I assume that you want to highlight the relative cell in col B when a cell in col F is selected - in which case

    Public LastSelectedRow As Long
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'check to see if there has been a previous selection in col F
    If LastSelectedRow = 0 Then LastSelectedRow = Target.Row
    Dim colOffset As Integer
    colOffset = -4
    Dim colCheck As String
    colCheck = "F"
    Dim iSect As Range
    Set iSect = Intersect(Target, Columns(colCheck))
    If Not iSect Is Nothing Then
    '1st remove the colour from the last selected row
        Cells(LastSelectedRow, colCheck).Offset(0, colOffset).Interior.ColorIndex = xlNone
        
        LastSelectedRow = Target.Row
        
    'now colour the cell you want
        Target.Offset(0, colOffset).Interior.ColorIndex = 6
    End If
    End Sub
    should work in compatability mode or not


    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    vendredi 9 mars 2012 02:53
  • Hi SGFan,

    Sorry for the delay.

    Please try the test.xls file from this link:

    https://skydrive.live.com/redir.aspx?cid=37142ebae462f7f1&resid=37142EBAE462F7F1!134&parid=root

    Also, you may consider saving the file as Macro-Enabled worksheet. If so, the file name should be like "filename.xlsm". Since I tested it with Excel 2007 just now and saved its file type as "xlsm". When I opened it from Excel 2003, it works fine. 

    Notice: 1. You need to install the Compatible pack for Excel 2003 from this link:
    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=3 

    2. You may need to set the security level of the Excel 2003 to enable the macro. Open Excel 2003, Tools-> Macro->Security -> low. I set it to low for test.

    Meanwhile, I have uploaded a test file(Excel 2007) for you as reference:

    https://skydrive.live.com/redir.aspx?cid=37142ebae462f7f1&resid=37142EBAE462F7F1!135&parid=root

    Hope it helps.

    yoyo


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    vendredi 9 mars 2012 03:07
    Modérateur
  • Is there a way to do a temporary highlight on a cell when you enter another one?

    i.e., when user enters cell F2, say, the macro would then colour or somehow place a border or highlight cell B2.

    I've googled and searched for last couple of days but I must not be asking the right question as nothing pertinent comes up.

    Thanks.

    You can do this w/o code (or minimal code support) with the following:

    Suppose you want H20 to change format when you select C20.

    Then, select H20 and create a conditional format with the formula =CELL("address")=CELL("address",$C$20) and specify whatever format you want.

    Now, when you select C20 and recalculate (F9) H20 will change to the format selected above.

    To skip the F9 requirement, use the minimal event procedure below:

    Option Explicit

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


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present

    vendredi 9 mars 2012 05:22
  • Hi SGFan,

    Sorry for the delay.

    Please try the test.xls file from this link:

    https://skydrive.live.com/redir.aspx?cid=37142ebae462f7f1&resid=37142EBAE462F7F1!134&parid=root

    Also, you may consider saving the file as Macro-Enabled worksheet. If so, the file name should be like "filename.xlsm". Since I tested it with Excel 2007 just now and saved its file type as "xlsm". When I opened it from Excel 2003, it works fine. 

    Notice: 1. You need to install the Compatible pack for Excel 2003 from this link:
    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=3 

    2. You may need to set the security level of the Excel 2003 to enable the macro. Open Excel 2003, Tools-> Macro->Security -> low. I set it to low for test.

    Meanwhile, I have uploaded a test file(Excel 2007) for you as reference:

    https://skydrive.live.com/redir.aspx?cid=37142ebae462f7f1&resid=37142EBAE462F7F1!135&parid=root

    Hope it helps.

    yoyo


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Thank you, Yoyo.  I was finally able to dl the files.  The links didn't work from the office.

    I see that I missed an important piece of information.  My apologies.  Trying to find where everyone had gone to from the ngs has taken up a lot of time and effort and it distracted me somewhat from this issue.  I actually needed code that would work for all cells in columns B and F, I just happened to make these two cells, B2 and F2, a particular example.

    So the code is very good but it only works for the one cell and I don't know how to change that.  However, looking at Tushar's example with condiitonal formatting made me realize that avoiding macros wherever possible, esp. in XL2007 and XL2010, 2 versions I don't care for, would be better.  I may be saving in 2003 format but sooner or later a user is going to save in 2007 or 2010 so best to avoid macros wherever possible to avoid all those confusing extensions for users.

    It's great code and it's going into my code snippets folder.  Thank you!

    • Modifié SGFan mardi 13 mars 2012 09:48
    mardi 13 mars 2012 09:40
  • Tushar, hi.  I think that conditional formatting (CF) might be the easiest way.  Thank you for the heads up. I tried your syntax in the CF box so that in B2, as my example, I put this in:

    =CELL("address")=CELL("address",$F2)

    Ideally, it would only be invoked when I entered F2 but that's not the case.  The minute I exit the CF box, all B cells are changed permanently and not just when a cell is entered in column F.

    Is there any way to fine-tune this so that the formatting is only invoked when a person enters the cell in column F on the same row as the cell on column B?  i.e.,

    F2 entered = format changes for B2

    F3 entered = format changes for B3

    F4 entered = format changes for B4, etc.?

    and without having to refresh either with F9 or a macro?  I've never had CF require a macro to work so hopefully the same ultimately is true here.

    Thanks for this!  I'd never have thought to look to CF for a solution.  :)

    mardi 13 mars 2012 09:56
  • Drat, I had answered this but somehow I deleted the message (boy I miss the ngs and a newsreader!  Those simple days are gone now.)

    Tushar thanks.  Conditional formatting (CF) would be ideal solution.  I tried your CF syntax but it just highlighted the cells permanently.  So now all the cells where I'd put the coding in referencing a cell in column F, show that CF change permanently in column B.

    I put this in to B2 making only the column reference an absolute address:

    =CELL("address")=CELL("address",$F2)

    What would be desired is for the special formatting to only show up when user enters F2 (affecting B2), F3 (affecting B3), F4 (affecting B4), etc., but when user clicks into any any other cell, would revert back to the original formatting.

    This is complicated by already having alternate row shading code in CF but I'll know how to work around that once there's working code to do this.  (It's usually always been just a question of hierarchy, where in order each bit of syntax goes.(

    Great idea to go with CF instead of a macro.  I don't like XL2007 or XL2010 so even though this file is saved in 2003 format, sooner or later a user is going to save in a later version and then complicate things with all those new extensions that confuse them so avoiding macros wherever possible is ideal.

    Thanks!  :)

    mardi 13 mars 2012 10:06
  • Tushar, hi.  I think that conditional formatting (CF) might be the easiest way.  Thank you for the heads up. I tried your syntax in the CF box so that in B2, as my example, I put this in:

    =CELL("address")=CELL("address",$F2)

    Ideally, it would only be invoked when I entered F2 but that's not the case.  The minute I exit the CF box, all B cells are changed permanently and not just when a cell is entered in column F.

    Is there any way to fine-tune this so that the formatting is only invoked when a person enters the cell in column F on the same row as the cell on column B?  i.e.,

    F2 entered = format changes for B2

    F3 entered = format changes for B3

    F4 entered = format changes for B4, etc.?

    and without having to refresh either with F9 or a macro?  I've never had CF require a macro to work so hopefully the same ultimately is true here.

    Thanks for this!  I'd never have thought to look to CF for a solution.  :)

    It is important that you select B2 before you enter the c.f. formula (this is because the c.f. formula has a relative address component).

    You will have to use either the 1 line VBA routine or F9 to see the change.  This is because Excel does not do any recalculation when someone selects a cell.  Since it doesn't recalculate, it doesn't re-evaluate any of the c.f. conditions.


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present

    mardi 13 mars 2012 20:26
  • CF as Tushar has posted will still require a bit of code or an F9 (caculate)
    This should do all you want and will work in 2003,2007 or 2010:

    Public LastSelectedRow As Long

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'check to see if there has been a previous selection in col F
    If LastSelectedRow = 0 Then LastSelectedRow = Target.Row
    Dim colOffset As Integer
    colOffset = -4
    Dim colCheck As String
    colCheck = "F"
    Dim iSect As Range
    Set iSect = Intersect(Target, Columns(colCheck))
    If Not iSect Is Nothing Then
    '1st remove the colour from the last selected row
        Cells(LastSelectedRow, colCheck).Offset(0, colOffset).Interior.ColorIndex = xlNone
       
        LastSelectedRow = Target.Row
       
    'now colour the cell you want
        Target.Offset(0, colOffset).Interior.ColorIndex = 6
    Else
     'set last selected row back to no fill as user has clicked out of column F
      Cells(LastSelectedRow, colCheck).Offset(0, colOffset).Interior.ColorIndex = xlNone

    End If
    end sub


    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    mercredi 14 mars 2012 01:59
  • Tushar, hi.  I think that conditional formatting (CF) might be the easiest way.  Thank you for the heads up. I tried your syntax in the CF box so that in B2, as my example, I put this in:

    =CELL("address")=CELL("address",$F2)

    Ideally, it would only be invoked when I entered F2 but that's not the case.  The minute I exit the CF box, all B cells are changed permanently and not just when a cell is entered in column F.

    Is there any way to fine-tune this so that the formatting is only invoked when a person enters the cell in column F on the same row as the cell on column B?  i.e.,

    F2 entered = format changes for B2

    F3 entered = format changes for B3

    F4 entered = format changes for B4, etc.?

    and without having to refresh either with F9 or a macro?  I've never had CF require a macro to work so hopefully the same ultimately is true here.

    Thanks for this!  I'd never have thought to look to CF for a solution.  :)

    In keeping with the minimal code philosohpy, see a new Excel tip that I created.  One of the variants of the different results I illustrate is what you want.

    Highlight row and column of active cell
    http://www.tushar-mehta.com/publish_train/xl_vba_cases/0121%20highlight%20row%20and%20col%20of%20selected%20cell.shtml


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present

    vendredi 23 mars 2012 15:39