Active cell font color change

[Rejje]

Hi!

I'm trying to make the font color of the selected range within a worksheet temporarily change to a specific rgb. When not selected THEN change back to original font color setting.

However I'm not very successful...

Paste these codes into worksheet event while having another worksheet activated.

I've have started with below code. This changes the cell color, not the font color. It works allthough not without some possibilities of imrovement.



Const activeFill As Long = 255 '<--Use sub 'a' below to find the right value for your color of choice.
Dim oldCell As Range
Dim oldCellFill As Long
Private Sub Worksheet_Activate()
Set oldCell = Selection
oldCellFill = Selection.Interior.Color
Selection.Interior.Color = activeFill
End Sub
Private Sub Worksheet_Deactivate()
If oldCellFill = 16777215 Then '<--Workaround for no fill
oldCell.Interior.ColorIndex = 0
Else
oldCell.Interior.Color = oldCellFill
End If
Set oldCell = Nothing
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If oldCellFill = 16777215 Then
oldCell.Interior.ColorIndex = 0
Else
oldCell.Interior.Color = oldCellFill
End If
Set oldCell = Target
oldCellFill = Target.Interior.Color
Target.Interior.Color = activeFill
End Sub
Sub a()
Debug.Print RGB(256, 0, 0)
End Sub


This is what I've tried for making font color temp change. Note there's no need for the workaround for no fill with font color (I think at least).

Const activeFontThemeColor As Long = -16776961 '<--Use sub 'a' below to find the right value for your color of choice.
Dim oldCell As Range
Dim oldCellFontThemeColor As Long
Private Sub Worksheet_Activate()
Set oldCell = Selection
oldCellFontThemeColor = Selection.Font.ThemeColor
Selection.Font.ThemeColor = activeFontThemeColor
End Sub
Private Sub Worksheet_Deactivate()
oldCell.Font.ThemeColor = oldCellFontThemeColor
Set oldCell = Nothing
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set oldCell = Target
oldCell.Font.ThemeColor = oldCellFontThemeColor
oldCellFontThemeColor = Target.Font.ThemeColor
Target.Font.ThemeColor = activeFontThemeColor
End Sub
Sub a()
Debug.Print RGB(256, 0, 0)
End Sub

I have also included an example (doesn't work):

6121

Does anyone have a clue of how to get this to work?

[mancubus]

pls read before cross-posting.

http://www.excelguru.ca/node/7

http://www.mrexcel.com/forum/showthread.php?p=2749738

[Rejje]

pls read before cross-posting.

http://www.excelguru.ca/node/7

http://www.mrexcel.com/forum/showthread.php?p=2749738

That's true and I didn't really even think about it. Thanks for letting me aware and from now one I'll stick to the general advice in this matter link provides!

Rejje