Change Format of Active Cell

[magmike]

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

 

[Don Guillett]

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

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

 

[magmike]

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

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]

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)?- Hide quoted text -

- Show quoted text -

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?

 

[GS]

magmike wrote :

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?

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.

 

[Gord]

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

 

[GS]

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]

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

 

[Gord]

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]

This code wipes any existing formatting from cells.

See code in my reply to your post to me.


Gord

 

[GS]

Gord explained :

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

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]

This code wipes any existing formatting from cells.

See code in my reply to your post to me.

Yeah, Mike H's code is definitely the way to go here. I really
appreciate you sharing that, Gord. Thanks again!

 

[magmike]

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






- Show quoted text -

Actually, this destroyed the Conditional formatting of EVERY cell once
I made my first selection! Thanks for trying though

 

[GS]

magmike formulated the question :

Actually, this destroyed the Conditional formatting of EVERY cell once
I made my first selection! Thanks for trying though

Not sure why this is happening for you. My existing cell formats remain
intact, and so the code works fine 'as is'!

 

[GS]

GS explained on 7/19/2011 :

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'!

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]

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