Border Around ACTIVE Cell
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Border Around ACTIVE Cell

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,502
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi:

    Does anyone know how to change the border (color or appearance) around the ACTIVE Cell. I have a spreadsheet that I created that has a lot of borders on it and whenever you tab to the cell your really need to look closely at the screen to see which cell is active. If I could change the borders around the active cell to a different clor that would be great. If you can't do this does anyone have a suggestion?

    Thanks,
    Mark

  2. #2

    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It would be easier to highlight selected cells with a different interior colour (and also make it easier to see what is selected):-

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Static PrevCell As Range
    If Not PrevCell Is Nothing Then
    PrevCell.Interior.ColorIndex = xlColorIndexNone
    End If
    Target.Interior.ColorIndex = 34
    Set PrevCell = Target
    End Sub

    Note : this procedure will remove any interior cell colours that you might have on your worksheet when the cells are selected.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You might also want to be aware that it will make undo useless

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mister H

    You could try this, it works for me:
    Use this event macro in your sheet

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("A1") = ActiveCell.Address
    End Sub

    Now select your area and use this formula in conditional formatting:
    =ADDRESS(ROW(),COLUMN())=$A$1
    Select a suitable color (say blue)

    Now the active cell (or top left cell of active range) will temporarily highlight in blue.

    Hope this works for you
    regards
    Derek

  5. #5
    Guest

    Default

    On 2002-03-12 18:20, Paul B wrote:
    You might also want to be aware that it will make undo useless

    Running ANY macro cancels the undo function.

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Derek, that brilliant! I love simple lateral thinking suggestions like that. If you email that to me (with any other details you have) I would like to put it in my next months newsletter along with your name, Website (if you have one) etc.



  7. #7
    Guest

    Default

    On 2002-03-12 21:16, Derek wrote:
    Hi Mister H

    You could try this, it works for me:
    Use this event macro in your sheet

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("A1") = ActiveCell.Address
    End Sub

    Now select your area and use this formula in conditional formatting:
    =ADDRESS(ROW(),COLUMN())=$A$1
    Select a suitable color (say blue)

    Now the active cell (or top left cell of active range) will temporarily highlight in blue.

    Hope this works for you
    regards
    Derek

    I'm getting some erratic behaviour with this procedure.
    I set the conditional format for all cells on a worksheet and sometimes the active cell turns blue but sometimes it will only turn blue when the cell is double-clicked. After double-clicking, the cell remains blue even after it is no longer the active cell.
    What could be causing this?

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, I noticed that too, that's why I suggested selecting a range first. I don't know why this is, perhaps 65536 cells is too much for it. Seems to works okay on a smaller area though. Perhaps someone knows the answer.

    Thanks Dave, I am really flattered by your offer, but perhaps we had better hold off on my immortalisation in view of its apparent irratic behaviour. Seems logically it should work but perhaps some things are too good to be true!!!


    Regards
    Derek

    [ This Message was edited by: Derek on 2002-03-13 01:33 ]

  9. #9
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's too cool an idea to let go so try this

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.FormatConditions.Delete
    Target.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
    Target.FormatConditions(1).Interior.ColorIndex = 27
    End Sub




  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Obviously this wouldn't be much use for much, but it's quite cool anyway. Try:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Target.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
    Target.FormatConditions(1).Interior.ColorIndex = Int((56 - 1 + 1) * Rnd + 1)
    End Sub

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com