Solved: Compare Cells within same row for whole sheet

[john3j]

Ok,

What I am trying to do seems quite difficult to accomplish because I have been trying for days to figure it out and have had no luck. I need to find a way to turn the background color of the cell for the column U if there are certain criteria met. The cell V needs to be red if cell S has a date that is less than the current date, or if cell T is blank or empty. I need the code to perform this for each row of the active sheet. Any help would be greatly appreciated!

Thanks,
John
:banghead:

[nst1107]

Welcome to the forum, John. Conditional formatting will probably do the job just as well, but here's a possible VBA solution:Dim c As Range
For Each c In Sheet1.Columns("U").Cells
If c > 4 And c < 10 Then c.Interior.Color = vbYellow
Next
For Each c In Sheet1.Columns("V").Cells
If c.Offset(, -3) < Date Or c.Offset(, -2) = vbNullString Then c.Interior.Color = vbRed
Next

This will likely take a long time to run through every cell in both columns, so it would be good to limit the rows it checks.

[john3j]

How would i limit the rows? also, I have another thread im trying to get an answer for. If you would, please look at it too?

[mdmackillop]

Dim c As Range
For Each c In Intersect(Sheet1.Columns("U"),sheet1.usedrange).Cells
If c > 4 And c < 10 Then c.Interior.Color = vbYellow
Next
For Each c In intersect(Sheet1.Columns("V"),sheet1.usedrange).Cells
If c.Offset(, -3) < Date Or c.Offset(, -2) = vbNullString Then c.Interior.Color = vbRed
Next

[john3j]

It will go through no more than 1000 rows. So how would I limit it to that? And will this make it run faster?

[john3j]

The code worked a little, but not exactly what I wanted to do. Here is the criteria of what needs to happen:

Cells in Column "U" should be red IF Date in Column "S" is less than or
equal to today's date AND there is no date is Column "U"
Cells in Column "X" should be red IF Date in Column "V" is less than or
equal to todays's date AND there is no date in Column "X"
Cells in Column "AA" should be red IF Date in Column "Y" is less than or
equal to today's date AND there is no date in Column "AA"

Is there a modification that can be made to the code to make it work like this?

Here is the full code as it stands...there is a lot of color coding going on here.


Range("A1").Select

Set MyRange = Range("y2:y1000")
MyRange.Select
For Each cell In MyRange

If cell.Value > 40086 Then
cell.EntireRow.Select
cell.EntireRow.Interior.ColorIndex = 6
End If
Next
Set MyRange = Range("C2:C1000")
MyRange.Select
For Each cell In MyRange

If cell.Value <= "08" Then
cell.EntireRow.Hidden = True
End If
Next

'This portion of the code Changes the color of rows based
' on the criteria in column AB
'This changes row to light green if cell contains "Contract Awarded"
Set MyRange = Range("AB2:AB1000")
For Each cell In MyRange

If cell.Value = "Contract Awarded" Then
cell.EntireRow.Select
cell.EntireRow.Interior.ColorIndex = 35
cell.EntireRow.Font.ColorIndex = 1
' break
End If
Next
'This changes row to light blue if cell contains "Part A Held"
Set MyRange = Range("AB2:AB1000")
MyRange.Select
For Each cell In MyRange

If cell.Value = "Part A Held" Then
cell.EntireRow.Select
cell.EntireRow.Interior.ColorIndex = 34
cell.EntireRow.Font.ColorIndex = 1
' break
End If
Next
'This changes row to pink if cell contains "Part B Accepted"
Set MyRange = Range("AB2:AB1000")
MyRange.Select
For Each cell In MyRange

If cell.Value = "Part B Accepted" Then
cell.EntireRow.Select
cell.EntireRow.Interior.ColorIndex = 38
cell.EntireRow.Font.ColorIndex = 1
' break
End If
Next
'This changes row to light yellow if cell contains "Part B Submitted"
Set MyRange = Range("AB2:AB1000")
MyRange.Select
For Each cell In MyRange

If cell.Value = "Part B Submitted" Then
cell.EntireRow.Select
cell.EntireRow.Interior.ColorIndex = 36
cell.EntireRow.Font.ColorIndex = 1
' break
End If
Next
'This changes row to white if cell contains "Planning"
Set MyRange = Range("AB2:AB1000")
MyRange.Select
For Each cell In MyRange

If cell.Value = "Planning" Then
cell.EntireRow.Select
cell.EntireRow.Interior.ColorIndex = 2
cell.EntireRow.Font.ColorIndex = 1
' break
End If
Next
'This changes row to white if cell contains "Postponed"
Set MyRange = Range("AB2:AB1000")
MyRange.Select
For Each cell In MyRange

If cell.Value = "Postponed" Then
cell.EntireRow.Select
cell.EntireRow.Interior.ColorIndex = 39
cell.EntireRow.Font.ColorIndex = 1
' break
End If
Next

'This hides the row if the cell contains "Cancelled", or "Deleted"
Set MyRange = Range("AB2:AB1000")
MyRange.Select
For Each cell In MyRange

If cell.Value = "Cancelled *If chosen, please state reason." Then
cell.EntireRow.Select
cell.EntireRow.Hidden = True
' break
End If
Next
Set MyRange = Range("AB2:AB1000")
For Each cell In MyRange
If cell.Value = "Deleted *If chosen, please state reason." Then
cell.EntireRow.Hidden = True
End If
Next
'This outlines every cell in the sheet
Set MyRange = Range("A2:AD1000")
MyRange.Select
Selection.Borders.LineStyle = xlContinuous
'This Removes extra rows that are empty
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.delete
Dim c As Range
For Each c In Intersect(Sheet1.Columns("U"), Sheet1.UsedRange).Cells
If c > 4 And c < 10 Then c.Interior.Color = vbYellow
Next
For Each c In Intersect(Sheet1.Columns("V"), Sheet1.UsedRange).Cells
If c.Offset(, -3) < Date Or c.Offset(, -2) = vbNullString Then c.Interior.Color = vbRed
Next
End Sub

[mdmackillop]

Can you post sample data to test?

[mdmackillop]

Use conditional formatting
=AND(U1="",S1<TODAY(),S1<>"")

[john3j]

That fomula works great. Bad news is they changed the form today. Now we need it to do the following.

Column "U" is to be red IF the date in Column "S" is equal to today's date
or has already passed AND there is no date in Column "T". IF there is a
date in Column "T" then disregard Column "S." In this case, Column "U"
should be red IF the date in Column "T" is equal to today's date or has
already passed.
Column "X" is to be red IF the date in Column "V" is equal to today's date
or has already passed AND there is no date in Column "W". IF there is a
date in Column "W" then disregard Column "V." In this case, Column "X"
should be red IF the date in Column "W" is equal to today's date or has
already passed.
Column "AA" is to be red IF the date in Column "Y" is equal to today's date
or has already passed AND there is no date in Column "Z". IF there is a
date in Column "Z" then disregard Column "Y." In this case, Column "AA"
should be red IF the date in Column "Z" is equal to today's date or has
already passed.

Can conditional formatting still be used to make this work? Please help me!

[mdmackillop]

Can you post your own attempts and I'll have a look.

[john3j]

Ok, one last thing. The conditional formatting works for each column but there is one problem. If you look in columns X and Y. Take Y14 for example. Y14 should not be red because it is emtpy with X14 at the same time. So if Y14 and X14 are empty, then no formatting should be applied, only what formatting is done by the VBA code. Let me know what you think guys. I appreciate your help.

[mdmackillop]

Just add the X column into the AND comparison
=AND(W1<TODAY(),W1<>"",X1<>"")