VBA format on conditions
 

I have the need to identify and mark large worksheets that we display during
twice weekly meetings. To make the data a little easier to read, we color
rows based on certian values. The code below does that great, but I need to
add another part that would also look at the cell which contains the date,
and mark it red if it today or past due. Can I do this?

Dim eRow As Long
Dim i As Long
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
Rows(i).EntireRow.Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End If

Thanks

VBA format on conditions
 

First I will note that you could accomplish the highlighting for both these
conditions using conditional formatting, but assuming for some reason you
need to do it through code the revised code is shown below. I would need to
know what column the date is in so for illustration I will just say it is in
column J (i.e. column # 10). Also note I eliminated the Cells....Select
since you can do this without needing to select the cells.

Dim eRow As Long
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
If Cells(i,10).Value <= Date Then 'fix column number in
Cells(i,10) as needed
.ColorIndex = 3 ' Red (assumes standard palette)
.Pattern = xlSolid
End If
End With


--
- K Dales


"Byron" wrote:

I have the need to identify and mark large worksheets that we display during
twice weekly meetings. To make the data a little easier to read, we color
rows based on certian values. The code below does that great, but I need to
add another part that would also look at the cell which contains the date,
and mark it red if it today or past due. Can I do this?

Dim eRow As Long
Dim i As Long
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
Rows(i).EntireRow.Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End If

Thanks

VBA format on conditions
 

I actually still want to color the entire row a different color, and just
color the font in the one cell red based on the date. Is that possible.

Byron

"K Dales" wrote:

First I will note that you could accomplish the highlighting for both these
conditions using conditional formatting, but assuming for some reason you
need to do it through code the revised code is shown below. I would need to
know what column the date is in so for illustration I will just say it is in
column J (i.e. column # 10). Also note I eliminated the Cells....Select
since you can do this without needing to select the cells.

Dim eRow As Long
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
If Cells(i,10).Value <= Date Then 'fix column number in
Cells(i,10) as needed
.ColorIndex = 3 ' Red (assumes standard palette)
.Pattern = xlSolid
End If
End With


--
- K Dales


"Byron" wrote:

I have the need to identify and mark large worksheets that we display during
twice weekly meetings. To make the data a little easier to read, we color
rows based on certian values. The code below does that great, but I need to
add another part that would also look at the cell which contains the date,
and mark it red if it today or past due. Can I do this?

Dim eRow As Long
Dim i As Long
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
Rows(i).EntireRow.Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End If

Thanks