If 80 in column A and date = today...
 

Hello,

I have a table with 200 to 500 entries, where 1 entries = 1 row.

I want a macro that will check for the number “80” in “column A”. If
not “80” then nothing.

If 80 in column “A” then for this row:

Check the information in column “E” : if empty or contains a date
later than today then nothing.

If column “E” contains a date earlier or equal to today then:
1. change the 80 for a “0” in column “A”
2. change whatever is in column “B” for also a “0”
3. change the color of the row (from column A to F) in pale blue.

Then that’s it.

Can anyone help me?

Thank you!

Souriane

If 80 in column A and date = today...
 

try this; it worked for me.
'-----------------------------------------
Sub souriane()

Dim c As Range
Dim myRange As Range
Dim myLastRow As Long
Dim myWS As Worksheet

Set myWS = ActiveWorkbook.Worksheets("Sheet1")
myLastRow = myWS.Cells(10000, 1).End(xlUp).Row
Set myRange = myWS.Range("a1:a" & myLastRow)

For Each c In myRange
If c.Value = 80 Then
If c.Offset(0, 4).Value = Date Then
c.Offset(0, 1).Value = 0
c.Value = 0
With Range("a" & c.Row & ":f" & c.Row).Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
End If
Next c

End Sub
'-------------------------------------------
:)
susan



On Nov 12, 9:45*am, Souriane wrote:
Hello,

I have a table with 200 to 500 entries, where 1 entries = 1 row.

I want a macro that will check for the number “80” *in “column A”. *If
not “80” then nothing.

If 80 in column “A” then for this row:

Check the information in column “E” : if empty or contains a date
later than today then nothing.

If column “E” contains a date earlier or equal to today then:
1. * * *change the 80 for a “0” in column “A”
2. * * *change whatever is in column “B” for also a “0”
3. * * *change the color of the row (from column A to F) in pale blue.

Then that’s it.

Can anyone help me?

Thank you!

Souriane

If 80 in column A and date = today...
 

missed the part about the dates being greater or less than today - try
this one (not extensively tested):

'---------------------------------------
Sub souriane()

Dim c As Range
Dim myRange As Range
Dim myLastRow As Long
Dim myWS As Worksheet

Set myWS = ActiveWorkbook.Worksheets("Sheet1")
myLastRow = myWS.Cells(10000, 1).End(xlUp).Row
Set myRange = myWS.Range("a1:a" & myLastRow)

For Each c In myRange
If c.Value = 80 Then
If c.Offset(0, 4).Value = "" Then
'do nothing
ElseIf c.Offset(0, 4).Value Date Then
'do nothing
ElseIf c.Offset(0, 4).Value <= Date Then
c.Offset(0, 1).Value = 0
c.Value = 0
With Range("a" & c.Row & ":f" & c.Row).Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
End If
Next c

End Sub
'--------------------------------------
susan



On Nov 12, 10:01*am, Susan wrote:
try this; it worked for me.
'-----------------------------------------
Sub souriane()

Dim c As Range
Dim myRange As Range
Dim myLastRow As Long
Dim myWS As Worksheet

Set myWS = ActiveWorkbook.Worksheets("Sheet1")
myLastRow = myWS.Cells(10000, 1).End(xlUp).Row
Set myRange = myWS.Range("a1:a" & myLastRow)

For Each c In myRange
If c.Value = 80 Then
* *If c.Offset(0, 4).Value = Date Then
* * * c.Offset(0, 1).Value = 0
* * * c.Value = 0
* * * With Range("a" & c.Row & ":f" & c.Row).Interior
* * * * *.ColorIndex = 34
* * * * *.Pattern = xlSolid
* * * End With
* *End If
End If
Next c

End Sub
'-------------------------------------------
:)
susan

On Nov 12, 9:45*am, Souriane wrote:



Hello,

I have a table with 200 to 500 entries, where 1 entries = 1 row.

I want a macro that will check for the number “80” *in “column A”. *If
not “80” then nothing.

If 80 in column “A” then for this row:

Check the information in column “E” : if empty or contains a date
later than today then nothing.

If column “E” contains a date earlier or equal to today then:
1. * * *change the 80 for a “0” in column “A”
2. * * *change whatever is in column “B” for also a “0”
3. * * *change the color of the row (from column A to F) in pale blue.

Then that’s it.

Can anyone help me?

Thank you!

Souriane- Hide quoted text -

- Show quoted text -

If 80 in column A and date = today...
 

Wow!!!! Thank you Susan! It work fine the first time! Nothing to
change except my sheet name. I am really impressed!

Thanks alot!

I appreciate you help very much!!!

Souriane

If 80 in column A and date = today...
 

you're welcome! and it helped a lot that you explained it clearly and
concisely exactly what you needed the macro to do.
:)
susan


On Nov 12, 7:56*pm, Souriane wrote:
Wow!!!! Thank you Susan! *It work fine the first time! *Nothing to
change except my sheet name. *I am really impressed!

Thanks alot!

I appreciate you help very much!!!

Souriane