Format cells based on two conditions
 

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.

Format cells based on two conditions
 

Right click sheet tabview codeinsert 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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shariq" wrote in message
...
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.

Format cells based on two conditions
 

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" wrote:

Right click sheet tabview codeinsert 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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shariq" wrote in message
...
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.

Format cells based on two conditions
 

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shariq" wrote in message
...
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" wrote:

Right click sheet tabview codeinsert 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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shariq" wrote in message
...
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.

Format cells based on two conditions
 

Everything appears to be working. Thank you very much!

"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shariq" wrote in message
...
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" wrote:

Right click sheet tabview codeinsert 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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shariq" wrote in message
...
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.