VBA Color Date if Past due
 

All

I need to color the font in a cell red based on if the date contained in
that cell is past due or not. The part that complicates this is that I also
need to color the whole line a different color based on other requirements,
as shown below. Where those to are contained on the same row, I need to
color the interior of the row one color, and the date another. Hope that is
clear. Ive had many wonderful suggestions on how to color the whole row, but
not individual text in individual cells. Sorry for the newguy persistence on
this.


Dim i As Long
eRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = eRow To 2 Step -1
With Rows(i).EntireRow.Interior
If Cells(i, 12).Value = "DOCK" Or Cells(i, 12).Value = "FROZ"
Or _
Cells(i, 12).Value = "Loan Repay" Or Cells(i, 12).Value =
"TRAN" Then
.ColorIndex = 4
.Pattern = xlSolid
End If
End With

VBA Color Date if Past due
 

Assume the due date is in Column "B"

Dim i As Long

eRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = eRow To 2 Step -1
With Rows(i).EntireRow.Interior
If Cells(i, 12).Value = "DOCK" Or _
Cells(i, 12).Value = "FROZ" Or _
Cells(i, 12).Value = "Loan Repay" Or _
Cells(i, 12).Value = "TRAN" Then
.ColorIndex = 4
.Pattern = xlSolid
End If
End With
if cells(i,"B").Value < Date then
cells(i,"B").Interior.ColorIndex = 3
end if
next

--
Regards,
Tom Ogilvy



"Byron" wrote in message
...
All

I need to color the font in a cell red based on if the date contained in
that cell is past due or not. The part that complicates this is that I
also
need to color the whole line a different color based on other
requirements,
as shown below. Where those to are contained on the same row, I need to
color the interior of the row one color, and the date another. Hope that
is
clear. Ive had many wonderful suggestions on how to color the whole row,
but
not individual text in individual cells. Sorry for the newguy persistence
on
this.


Dim i As Long
eRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = eRow To 2 Step -1
With Rows(i).EntireRow.Interior
If Cells(i, 12).Value = "DOCK" Or Cells(i, 12).Value = "FROZ"
Or _
Cells(i, 12).Value = "Loan Repay" Or Cells(i, 12).Value =
"TRAN" Then
.ColorIndex = 4
.Pattern = xlSolid
End If
End With

VBA Color Date if Past due
 

This sets column B to a different column if the date is less than today

erow = Cells(Rows.Count, 12).End(xlUp).Row
For i = erow To 2 Step -1
If Cells(i, 12).Value = "DOCK" Or Cells(i, 12).Value = "FROZ" Or _
Cells(i, 12).Value = "Loan Repay" Or Cells(i, 12).Value = "TRAN"
Then
With Rows(i).EntireRow.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End If
With Cells(i, "B")
If .Value < Date Then
.Interior.ColorIndex = 38
End If
End With
Next i

But I would still do this with Conditional Formatting personally

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Byron" wrote in message
...
All

I need to color the font in a cell red based on if the date contained in
that cell is past due or not. The part that complicates this is that I
also
need to color the whole line a different color based on other
requirements,
as shown below. Where those to are contained on the same row, I need to
color the interior of the row one color, and the date another. Hope that
is
clear. Ive had many wonderful suggestions on how to color the whole row,
but
not individual text in individual cells. Sorry for the newguy persistence
on
this.


Dim i As Long
eRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = eRow To 2 Step -1
With Rows(i).EntireRow.Interior
If Cells(i, 12).Value = "DOCK" Or Cells(i, 12).Value = "FROZ"
Or _
Cells(i, 12).Value = "Loan Repay" Or Cells(i, 12).Value =
"TRAN" Then
.ColorIndex = 4
.Pattern = xlSolid
End If
End With