View Full Version : [SOLVED:] Changing a cell font color upon cell entry & exit
simora
02-10-2017, 12:35 AM
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
02-10-2017, 04:14 AM
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.
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
02-10-2017, 12:49 PM
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.
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
02-17-2017, 09:36 PM
SamT : (http://www.vbaexpress.com/forum/member.php?6494-SamT)
Thanks for the additional info. Makes sense.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.