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

[Souriane]

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

 

[Susan]

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

 

[Susan]

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

 

[Souriane]

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

 

[Susan]

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

susan