Format cells based on two conditions

[Shariq]

Hello,

In Column A are Numbers.

In Column C are Dates.

I'd like to format cells in Column A and Column C based on these conditions:
If the Number in Column A is greater than 90, and if the Date in Column C is
older than 30 days old, fill both cells color red. I prefer to do this in VBA.

Just to clarify, here's an example:

If the cell value in A1 is 91, and the date in C1 is 1/1/2009, I'd like to
have both cells A1 and C1 filled with the color red.

Thanks in advance for your help.

 

[Don Guillett]

Right click sheet tab>view code>insert this. Now, when you change col A the
macro will fire
Could also be designed to fire on change of col C, if desired.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column <> 1 Then Exit Sub
With Target
.Interior.ColorIndex = 0
.Offset(, 2).Interior.ColorIndex = 0
If .Value > 90 And Date - .Offset(, 2) > 30 Then
.Interior.ColorIndex = 6
.Offset(, 2).Interior.ColorIndex = 6
End If
End With
End Sub

 

[Shariq]

This is helpful, thank you. Is it possible to run this code on all of Col A,
instead of just when Col A is changed? Maybe some kind of loop would work?

 

[Don Guillett]

sub runcola()
for i=1 to cells(rows.count,1).end(xlup).row

With cells(i,1)
.Interior.ColorIndex = 0
.Offset(, 2).Interior.ColorIndex = 0
If .Value > 90 And Date - .Offset(, 2) > 30 Then
.Interior.ColorIndex = 6
.Offset(, 2).Interior.ColorIndex = 6
End If
End With
next i
end sub

 

[Shariq]

Everything appears to be working. Thank you very much!