Change Format of Active Cell

Discussion in 'Microsoft Excel Programming' started by magmike, Jul 13, 2011.

  1. magmike

    magmike Guest

    In Excel 2007, on WinXP SP3,

    I would like to be able to change the format of the currently active
    cell to make it more easily visible at a quick glance. Currently, the
    active cell is just outlined in a thick black line. Is there a way to
    change conditional formatting to change the fill color and/or the
    outline color of the currently selective/active cell?

    Thanks in advance,

    magmike
     
    magmike, Jul 13, 2011
    #1
    1. Advertisements

  2. magmike

    Don Guillett Guest

    Right click sheet tab>view code>insert this. Now,when you select a
    cell it will hilite it. Does NOT change other formatting.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.FormatConditions.Delete
    'With Target.EntireRow
    'With Cells(Target.Row, 1).Resize(, 2)
    With Target
    .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
    .FormatConditions(1).Interior.ColorIndex = 6 '35
    End With
    End Sub
     
    Don Guillett, Jul 14, 2011
    #2
    1. Advertisements

  3. magmike

    magmike Guest

    Supercool, and easy!
    How would you modify this to change the selections text color if it is
    white, to black (but only for the selection period)?
     
    magmike, Jul 15, 2011
    #3
  4. magmike

    magmike Guest

    I am having problems with my Conditional formatting. I have quite a
    few rules that change an entire row's fill color and sometimes text
    formatting based on the text in a certain column. When I use this code
    in a sheet where this conditional formatting is present, when I make
    my first selection, all of the conditional formatting changes
    instantly disappear.

    Any ideas?
     
    magmike, Jul 15, 2011
    #4
  5. magmike

    GS Guest

    magmike wrote :
    The code in the Worksheet_SelectionChange event runs every time you
    'select' other cells. Perhaps you want to use the Worksheet_Change
    event so your code only runs when cells you specify are edited/changed.
     
    GS, Jul 15, 2011
    #5
  6. magmike

    Gord Guest

    This may interest you as a permanent fix for Excel 2007 and not
    require any VBA or add-ins.

    You can add or modify a key in the Registry if you are comforatble
    hacking in the Regsitry.

    Best to create a Restore Point before attempting any changes.

    Start>Run regedit.exe

    HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options

    On right-side pane right-click on "Options6" and modify>decimal number
    from 32 to 16.

    If you don't have an Options6 you must create it by adding a new DWORD
    value

    Edit>New>DWORD

    Rename to Options6 and set decimal value to 16

    Selected cell(s) will be Black


    Gord Dibben MS Excel MVP
     
    Gord, Jul 15, 2011
    #6
  7. magmike

    GS Guest

    Gord, your suggestion doesn't address the active cell, only those
    selected along with it (if any).

    Not sure why the active cell indicator doesn't work well enough for
    magmike, but maybe '_SelectionChange' is the right event to use with
    appropriate code. Problem is how to manage non-active cells once
    active, and so maybe a global variable to hold its address, which gets
    used to clear formatting BEFORE the current active cell's address gets
    put into it for next time.
     
    GS, Jul 15, 2011
    #7
  8. magmike

    GS Guest

    Example code...

    In the module behind the sheet... (right-click sheet tab and choose
    'View Code' from the popup menu)

    Option Explicit

    Dim msActiveAddr As String

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If msActiveAddr <> "" Then Range(msActiveAddr).ClearFormats
    Target.Interior.Color = vbRed: msActiveAddr = Target.Address
    End Sub
     
    GS, Jul 15, 2011
    #8
  9. magmike

    Gord Guest

    Thanks Garry

    I uninstalled 2007 a while back so had no way to test.

    I originally posted that "fix" a couple years ago when someone wanted
    a better visual on a selection of multiple cells.

    Forgot about a single selection.

    I like this from Mike H. which restores existing formatting of any
    type.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'cell highlighter
    'Mike H.........does not destroy existing formats
    Cells.FormatConditions.Delete
    With Target
    .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
    With .FormatConditions(1)
    .Interior.ColorIndex = 6
    End With
    End With
    End Sub


    Gord
     
    Gord, Jul 15, 2011
    #9
  10. magmike

    Gord Guest

    This code wipes any existing formatting from cells.

    See code in my reply to your post to me.


    Gord
     
    Gord, Jul 16, 2011
    #10
  11. magmike

    GS Guest

    Gord explained :
    Hmm..! I'd have to play around with this some to see how it might work
    with magmike's scenario. Large thanks for posting back with this...
     
    GS, Jul 16, 2011
    #11
  12. magmike

    GS Guest

    Yeah, Mike H's code is definitely the way to go here. I really
    appreciate you sharing that, Gord. Thanks again!
     
    GS, Jul 16, 2011
    #12
  13. magmike

    magmike Guest

    Actually, this destroyed the Conditional formatting of EVERY cell once
    I made my first selection! Thanks for trying though ;)
     
    magmike, Jul 19, 2011
    #13
  14. magmike

    GS Guest

    magmike formulated the question :
    Not sure why this is happening for you. My existing cell formats remain
    intact, and so the code works fine 'as is'!
     
    GS, Jul 19, 2011
    #14
  15. magmike

    GS Guest

    GS explained on 7/19/2011 :
    Just to clarify, I did not have any CF. After putting CF in place I see
    you're correct! Bummer.., this WAS looking good up to now! Ugh!!!
     
    GS, Jul 19, 2011
    #15
  16. magmike

    GS Guest

    Try...

    Option Explicit

    Dim msActiveAddr As String

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Range(msActiveAddr).Interior
    If msActiveAddr <> "" Then .ColorIndex = xlColorIndexNone
    End With
    With Target
    msActiveAddr = .Address
    .Interior.ColorIndex = 6
    End With
    End Sub
     
    GS, Jul 19, 2011
    #16
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
There are no similar threads yet.
Loading...