Changing a cell font color upon cell entry & exit

[simora]

Is there a way to change a cell font color in a specific set of ranges on a worksheet
( Range ("B7:B200") & Range ("E7:E200") ) when you click on a cell in that range to begin typing into it.

I want the cell font color to be white.

If I click on that cell, as soon as I begin typing into it,( Or as soon as I click on the Cell )
I want the cell font color to go back to black until I exit,( So that I can see what's typed into it)
then resort to white again when I exit the cell. ( Or wait 4 seconds , Then change the font color back)

[vcoolio]

Hello Simora,

Try the following Worksheet_Change event code in the required sheet module:-



Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If ActiveCell.Column = 1 Then
ActiveCell.Font.Color = vbBlack
Else: Target.Font.Color = vbWhite
End If

If ActiveCell.Column = 5 Then
ActiveCell.Font.Color = vbBlack
Else: Target.Font.Color = vbWhite
End If

End Sub



I hope that this helps.

Cheerio,
vcoolio.

[SamT]

Option Explicit

Dim BlackCellAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If BlackCellAddress <> "" Then Range(BlackCellAddress).Font.ColorIndex = 2
If Target.Address <> BlackCellAddress Then BlackCellAddress = ""

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B7:B200")) Is Nothing _
And Intersect(Target, Range("E7:E200")) Is Nothing _
Then Exit Sub

With Target
BlackCellAddress = .Address
.Font.ColorIndex = 1
End With

End Sub

[simora]

vcoolio (http://www.vbaexpress.com/forum/member.php?55066-vcoolio) Thanks:

I had tried something like that and for some reason it did not work.
Your code did not do what I wanted either.
I am already using the Worksheet_Change(ByVal Target As Range) for other changes.

------------------------------

SamT (http://www.vbaexpress.com/forum/member.php?6494-SamT) Thanks:

Your code worked exactly as intended.

Again Thanks all.

[SamT]

I am already using the Worksheet_Change(ByVal Target As Range) for other changes.

Event subs, Change, selection, etc, Should really only contain enough code to determine which other subs, that actually do the work, should be run.
For example

Option Explicit

Dim BlackCellAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Next two lines Required for all cells For proper funtion of SetFontColorBLack
If BlackCellAddress <> "" Then Range(BlackCellAddress).Font.ColorIndex = 2
If Target.Address <> BlackCellAddress Then BlackCellAddress = ""

'Standard Event check
If Target.Count > 1 Then Exit Sub

'Select Sub to run
If Not Intersect(Target, Range("B7:B200")) Is Nothing Then SetFontColorBlack Target
If Not Intersect(Target, Range("E7:E200")) Is Nothing Then SetFontColorBlack Target
'If not Intersect(Target, SomeOtherRange) is Nothing Then SomeOtherSub Target
'Etc

End Sub


Private Sub SetFontColorBlack(Target As Range)
With Target
BlackCellAddress = .Address
.Font.ColorIndex = 1
End With
End Sub


Private Sub SomeOtherSub(Target as Range)
'Blah Blah Blah
End Sub

[simora]

SamT : (http://www.vbaexpress.com/forum/member.php?6494-SamT)

Thanks for the additional info. Makes sense.