changing font colour of row when cell contains date

J

jason

seems easy...and probably is.
(solution maybe possible through conditional formatting...but i don't
know how!may only be possible through VBA)

have a database that is 1000 rows long and 10 columns wide.
column 4 cells either contain a date or is blank.
the font of the database is red when the cell in column 4 is blank.
at present the user has to manually change the font colour of rows to
black after they have entered a date into cells in column 4.

can this font colour be automatically changed when a date is entered?

any help greatly appreciated

Jason
 
J

Jan Karel Pieterse

Hi Jason,
can this font colour be automatically changed when a date is entered?
Click to expand...

Yes, use the Formula Is option and enter this formula:

=NOT(ISBLANK(D1))

DO this with the entire column D selected and cell D1 as the active
cell.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
R

Rick

Hi,

You could check the internet to see if OnEntry would help
you. It works something like this (this is just an
example of a change to an activecell):

Sub OnEntryOn()
ActiveSheet.OnEntry = "ChangeColor"
End Sub

Sub ChangeColor()
ActiveCell.Font.ColorIndex = 37
End Sub

Sub OnEntryOff()
ActiveSheet.OnEntry = ""
End Sub

Further programming could be added to do exactly what you
need.

Rick
 
R

Rick

Jason,

Did you figure it out yet?

I had some more time today. There's other ways to write
this, but this is what came first to my mind.

Sub ColorsRed()
Rem: To Reset Colors
Dim Cel As Variant: Dim i As Integer

For Each Cel In Range("D1:D1000")
If IsEmpty(Cel) Then

For i = -3 To -1
Cel.Offset(0, i).Font.ColorIndex = 3 'Red
Next i
For i = 1 To 6
Cel.Offset(0, i).Font.ColorIndex = 3 'Red
Next i

Else

For i = -3 To -1
Cel.Offset(0, i).Font.ColorIndex =
xlAutomatic 'Black
Next i
For i = 1 To 6
Cel.Offset(0, i).Font.ColorIndex =
xlAutomatic 'Black
Next i

End If
Cel.Font.ColorIndex = 7 'Purple
Next Cel

End Sub

Sub OnEntryOnRev1()
ActiveSheet.OnEntry = "ChangeColorRev1"
End Sub

Sub ChangeColorRev1()
Dim i As Integer

For i = -3 To -1
ActiveCell.Offset(0, i).Font.ColorIndex = xlAutomatic
Next i

For i = 1 To 6
ActiveCell.Offset(0, i).Font.ColorIndex = xlAutomatic
Next i
End Sub

Sub OnEntryOffRev1()
ActiveSheet.OnEntry = ""
End Sub

I hope that helps.

Rick
 
Top