Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional format with 3 conditions | Excel Worksheet Functions | |||
Cond Format - 7 conditions | Excel Discussion (Misc queries) | |||
Conditional Format - 3 conditions | Excel Worksheet Functions | |||
Two conditions for same format?? | Excel Discussion (Misc queries) | |||
Format Conditions | Excel Programming |