Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Past Date Calculation | Excel Discussion (Misc queries) | |||
past cell by date | Excel Worksheet Functions | |||
When I open my past invoice it keeps changing date to todays date | Excel Worksheet Functions | |||
need to show when date is past due | Excel Worksheet Functions | |||
Past date in column to change color | Excel Worksheet Functions |