VBA Color Date if Past due

[Byron]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>