Proper way to write a conditional statement with ands

[DKY]

How would I translate this into VBS?? I know what I want to do, but don't know the proper way to do this.

If the value in column K<>"" AND <>"0000" AND the value in column M>"(Now()-7)" then delete the row.

Help???

[Norman Jones]

Hi DKY,

Try:

Public Sub Test03()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Dim rng As Range

Set sh = Sheets("Sheet1") '<<====== CHANGE

Lrow = Cells(Rows.Count, "K").End(xlUp).Row

For i = Lrow To 1 Step -1
Set rng = Range("K" & i)
If Not IsEmpty(rng) Then
If rng.Value <> "0000" Then
If rng.Offset(0, 2).Value = Date - 7 Then
rng.EntireRow.Delete
End If
End If
End If
Next

End Sub

Change Sheet1 to the name of your worksheet.

Until you are happy that this macro properly reflects your intentions, run
it on a copy of your workbook.

---
Regards,
Norman



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> How would I translate this into VBS?? I know what I want to do, but
> don't know the proper way to do this.
>
> If the value in column K<>"" AND <>"0000" AND the value in column
> M>"(Now()-7)" then delete the row.
>
> Help???
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[Mike Fogleman]

If Range("K1").Value <> "" And Range("K1").Value <> "0000" And _
Range("M1").Value > Now - 7 Then Range("K1").EntireRow.Delete

This will delete Row 1 when the 3 conditions are met.

Mike F
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> How would I translate this into VBS?? I know what I want to do, but
> don't know the proper way to do this.
>
> If the value in column K<>"" AND <>"0000" AND the value in column
> M>"(Now()-7)" then delete the row.
>
> Help???
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[DKY]

Thanks for the quick response Norman, but the code below doesn't seem to do anything in column M. How would I adjust it so that it compares the date in column M, not in K and still have it compare values in column K such as the blank value "" and the 0000 value "0000". Thanks in advance.

Hi DKY,

Try:

Public Sub Test03()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Dim rng As Range

Set sh = Sheets("Sheet1") '<<====== CHANGE

Lrow = Cells(Rows.Count, "K").End(xlUp).Row

For i = Lrow To 1 Step -1
Set rng = Range("K" & i)
If Not IsEmpty(rng) Then
If rng.Value <> "0000" Then
If rng.Offset(0, 2).Value = Date - 7 Then
rng.EntireRow.Delete
End If
End If
End If
Next

End Sub

Change Sheet1 to the name of your worksheet.

Until you are happy that this macro properly reflects your intentions, run
it on a copy of your workbook.

---
Regards,
Norman



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> How would I translate this into VBS?? I know what I want to do, but
> don't know the proper way to do this.
>
> If the value in column K<>"" AND <>"0000" AND the value in column
> M>"(Now()-7)" then delete the row.
>
> Help???
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[DKY]

Mike,
This code seems to do what I want it to do but how would I get this to work through all the rows until it gets to the last row with any data? I also noticed that the code would get rid of my header so I started it on K2 instead. In order to achieve the ability to have the macro go on down the rows until there are none left I think I would have to do a Do While loop. Is that correct?

If Range("K1").Value <> "" And Range("K1").Value <> "0000" And _
Range("M1").Value > Now - 7 Then Range("K1").EntireRow.Delete

This will delete Row 1 when the 3 conditions are met.

Mike F
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> How would I translate this into VBS?? I know what I want to do, but
> don't know the proper way to do this.
>
> If the value in column K<>"" AND <>"0000" AND the value in column
> M>"(Now()-7)" then delete the row.
>
> Help???
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[DKY]

@ Mike

Here's what I got and its not working, Its like in an endless loop or something, it just freezes up my screen.

Please Login or Register  to view this content.

[Norman Jones]

Hi DKY,

I misread your original request.

Change

>> If rng.Offset(0, 2).Value = Date - 7 Then

to:

If rng.Offset(0, 2).Value > Date - 7 Then


---
Regards,
Norman



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks for the quick response Norman, but the code below doesn't seem to
> do anything in column M. How would I adjust it so that it compares the
> date in column M, not in K and still have it compare values in column K
> such as the blank value "" and the 0000 value "0000". Thanks in
> advance.
>
> Norman Jones Wrote:
>> Hi DKY,
>>
>> Try:
>>
>> Public Sub Test03()
>>
>> Dim sh As Worksheet
>> Dim i As Long
>> Dim Lrow As Long
>> Dim rng As Range
>>
>> Set sh = Sheets("Sheet1") '<<====== CHANGE
>>
>> Lrow = Cells(Rows.Count, "K").End(xlUp).Row
>>
>> For i = Lrow To 1 Step -1
>> Set rng = Range("K" & i)
>> If Not IsEmpty(rng) Then
>> If rng.Value <> "0000" Then
>> If rng.Offset(0, 2).Value = Date - 7 Then
>> rng.EntireRow.Delete
>> End If
>> End If
>> End If
>> Next
>>
>> End Sub
>>
>> Change Sheet1 to the name of your worksheet.
>>
>> Until you are happy that this macro properly reflects your intentions,
>> run
>> it on a copy of your workbook.
>>
>> ---
>> Regards,
>> Norman

[Norman Jones]

Hi DKY,

Given your reference to a header row in your response to Mike, amend:

>> For i = Lrow To 1 Step -1

to

For i = Lrow To 2 Step -1

---
Regards,
Norman



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks for the quick response Norman, but the code below doesn't seem to
> do anything in column M. How would I adjust it so that it compares the
> date in column M, not in K and still have it compare values in column K
> such as the blank value "" and the 0000 value "0000". Thanks in
> advance.
>
> Norman Jones Wrote:
>> Hi DKY,
>>
>> Try:
>>
>> Public Sub Test03()
>>
>> Dim sh As Worksheet
>> Dim i As Long
>> Dim Lrow As Long
>> Dim rng As Range
>>
>> Set sh = Sheets("Sheet1") '<<====== CHANGE
>>
>> Lrow = Cells(Rows.Count, "K").End(xlUp).Row
>>
>> For i = Lrow To 1 Step -1
>> Set rng = Range("K" & i)
>> If Not IsEmpty(rng) Then
>> If rng.Value <> "0000" Then
>> If rng.Offset(0, 2).Value = Date - 7 Then
>> rng.EntireRow.Delete
>> End If
>> End If
>> End If
>> Next
>>
>> End Sub
>>
>> Change Sheet1 to the name of your worksheet.
>>
>> Until you are happy that this macro properly reflects your intentions,
>> run
>> it on a copy of your workbook.
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "DKY" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >
>> > How would I translate this into VBS?? I know what I want to do, but
>> > don't know the proper way to do this.
>> >
>> > If the value in column K<>"" AND <>"0000" AND the value in column
>> > M>"(Now()-7)" then delete the row.
>> >
>> > Help???
>> >
>> >
>> > --
>> > DKY
>> >
>> ------------------------------------------------------------------------
>> > DKY's Profile:
>> > http://www.excelforum.com/member.php...o&userid=14515
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=387731
>> >
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[STEVE BELL]

i does not increment unless the if statement runs,
so it will stay = 2 forever

put i = i + 1
after the End If
and remove the quotes from i = "2"

Public Sub conditional()
Dim i as long
i = 2
Do While Range("A" & i).Value <> ""
If Range("K" & i).Value <> "" And Range("K" & i).Value <> "0000" And
Range("M" & i).Value > Now - 9 Then
Range("K" & i).EntireRow.Delete
End If
i = i + 1
Loop
End Sub

--
steveB

Remove "AYN" from email to respond
"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> @ Mike
>
> Here's what I got and its not working, Its like in an endless loop or
> something, it just freezes up my screen.
>
>
> Code:
> --------------------
> Public Sub conditional()
> Dim i
> i = "2"
> Do While Range("A" & i).Value <> ""
> If Range("K" & i).Value <> "" And Range("K" & i).Value <> "0000" And
> Range("M" & i).Value > Now - 9 Then
> Range("K" & i).EntireRow.Delete
> i = (i = 1)
> End If
> Loop
> End Sub
> --------------------
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[DKY]

Okay, thanks Steve. This now runs but when it runs its quirky. Just to see exactly what it was deleting I had it put an X in the far right column instead of delete. Then I colored and sorted. It seems like this does ignore anything with a blank or a 0000 in column K but it deletes everything else, no matter what the value of column M is. The values in column M were set up as general so I changed them to date to see if that would make a difference but it didn't. They are in this format, mm/dd/yy in every cell and I can't figure out why it deletes all of them, even if they are greater than the specified date. I even had it put the Now- 9 in the cell instead of the X and the dates are coming out okay. It doesn't make sense to me. Help?

i does not increment unless the if statement runs,
so it will stay = 2 forever

put i = i + 1
after the End If
and remove the quotes from i = "2"

Public Sub conditional()
Dim i as long
i = 2
Do While Range("A" & i).Value <> ""
If Range("K" & i).Value <> "" And Range("K" & i).Value <> "0000" And
Range("M" & i).Value > Now - 9 Then
Range("K" & i).EntireRow.Delete
End If
i = i + 1
Loop
End Sub

--
steveB

[Mike Fogleman]

Actually Norman has the better loop method because whenever you delete rows,
you should always start at the bottom of the list and work your way up. Here
is Norman's original loop with the changes for stopping before the Header
and Date comparison.

Public Sub Test03()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Dim rng As Range

Set sh = Sheets("Sheet1") '<<====== CHANGE

Lrow = Cells(Rows.Count, "K").End(xlUp).Row 'Finds the number of rows
in column K - if this is not a good column to determine the length of your
list then use a column that will.

For i = Lrow To 2 Step -1 'Loops from bottom to top- stops @ row 2.
Set rng = Range("K" & i)
If Not IsEmpty(rng) Then
If rng.Value <> "0000" Then
If rng.Offset(0, 2).Value > Date - 7 Then
rng.EntireRow.Delete
End If
End If
End If
Next

End Sub

Change Sheet1 to the name of your worksheet.

Until you are happy that this macro properly reflects your intentions, run
it on a copy of your workbook.

Mike F

"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> @ Mike
>
> Here's what I got and its not working, Its like in an endless loop or
> something, it just freezes up my screen.
>
>
> Code:
> --------------------
> Public Sub conditional()
> Dim i
> i = "2"
> Do While Range("A" & i).Value <> ""
> If Range("K" & i).Value <> "" And Range("K" & i).Value <> "0000" And
> Range("M" & i).Value > Now - 9 Then
> Range("K" & i).EntireRow.Delete
> i = (i = 1)
> End If
> Loop
> End Sub
> --------------------
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[Mike Fogleman]

I noticed you changed the date comparison to 9. You will need to do that in
the below code also.

Mike F
"Mike Fogleman" <[email protected]> wrote in message
news:[email protected]...
> Actually Norman has the better loop method because whenever you delete
> rows, you should always start at the bottom of the list and work your way
> up. Here is Norman's original loop with the changes for stopping before
> the Header and Date comparison.
>
> Public Sub Test03()
>
> Dim sh As Worksheet
> Dim i As Long
> Dim Lrow As Long
> Dim rng As Range
>
> Set sh = Sheets("Sheet1") '<<====== CHANGE
>
> Lrow = Cells(Rows.Count, "K").End(xlUp).Row 'Finds the number of rows
> in column K - if this is not a good column to determine the length of your
> list then use a column that will.
>
> For i = Lrow To 2 Step -1 'Loops from bottom to top- stops @ row 2.
> Set rng = Range("K" & i)
> If Not IsEmpty(rng) Then
> If rng.Value <> "0000" Then
> If rng.Offset(0, 2).Value > Date - 7 Then
> rng.EntireRow.Delete
> End If
> End If
> End If
> Next
>
> End Sub
>
> Change Sheet1 to the name of your worksheet.
>
> Until you are happy that this macro properly reflects your intentions, run
> it on a copy of your workbook.
>
> Mike F
>
> "DKY" <[email protected]> wrote in message
> news:[email protected]...
>>
>> @ Mike
>>
>> Here's what I got and its not working, Its like in an endless loop or
>> something, it just freezes up my screen.
>>
>>
>> Code:
>> --------------------
>> Public Sub conditional()
>> Dim i
>> i = "2"
>> Do While Range("A" & i).Value <> ""
>> If Range("K" & i).Value <> "" And Range("K" & i).Value <> "0000" And
>> Range("M" & i).Value > Now - 9 Then
>> Range("K" & i).EntireRow.Delete
>> i = (i = 1)
>> End If
>> Loop
>> End Sub
>> --------------------
>>
>>
>> --
>> DKY
>> ------------------------------------------------------------------------
>> DKY's Profile:
>> http://www.excelforum.com/member.php...o&userid=14515
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=387731
>>
>
>

[DKY]

Okay, I went to the code that Norman had posted and tried running it. It says compile error: syntax error. Then it highlights the first line in the code
Public Sub Test03()

[Norman Jones]

Hi DKY,

What is the error message you receive?


---
Regards,
Norman



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> Okay, I went to the code that Norman had posted and tried running it.
> It says compile error: syntax error. Then it highlights the first
> line in the code
> Public Sub Test03()
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[DKY]

Found it, nevermind. When I copied and pasted it, it put carriage returns in the comment so once I got rid of those it was working fine. Problem is, it seems to be deleting too many rows. When I do it manually on my spreadsheet I get 783 rows. When I open the spreadsheet and run the macro I end up with 578 rows left. I've got to play with it some more. Maybe I can figure out a way to have it put the letter x in the last column (column x) instead of deleting it so that I can see which one's its going to delete. I will try that later.

[DKY]

Okay, I've modified the code to add the blanks part. But the only thing its doing that I can't seem to figure out is the same thing it was doing with Mike's code above. I must be doing something wrong here. Its not deleting the rows where k=0000 and its not deleting the rows where k is blank which is what I want, that part is perfect. But when k has a value that's not 0000 I wanted it to delete those rows where M > Date - 9. Its almost as if it doesnt even look at column M and it deletes the row anyway. Here's my code right now (Ignore the put the letter x in the x column part)

Please Login or Register  to view this content.

[Norman Jones]

Hi DKY,

To check operation, comment out the line:

rng.EntireRow.Delete

and inssert immdiately after the new line:

rng.EntireRow.Interior.ColorIndex = 36

When you run the procedure, instead of deleting rows, it will color them.

If, and when, you are happy, delete the new line and uncomment the delete
line.


---
Regards,
Norman



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> Found it, nevermind. When I copied and pasted it, it put carriage
> returns in the comment so once I got rid of those it was working fine.
> Problem is, it seems to be deleting too many rows. When I do it
> manually on my spreadsheet I get 783 rows. When I open the spreadsheet
> and run the macro I end up with 578 rows left. I've got to play with it
> some more. Maybe I can figure out a way to have it put the letter x in
> the last column (column x) instead of deleting it so that I can see
> which one's its going to delete. I will try that later.
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[DKY]

That just colored the rows that my x's are in. Either way, for some reason its like it doesn't recognize column M. I don't get it.

[Norman Jones]

Hi DKY,

If you want to send a copy of your workbook, I will try to resolve.

Delete/replace any sensitive data. Mark the rows that meet your deletion
criteria.

nXorman_jXones@btXconnectDOTcom

(replace dot and remove each X) :

---
Regards,
Norman



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> That just colored the rows that my x's are in. Either way, for some
> reason its like it doesn't recognize column M. I don't get it.
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[DKY]

I just emailed it to you Norman. Thanks for taking the time to look at it.

[Norman Jones]

Hi DKY,

I received your workbook.

The data in column M on Sheet S2661060 are not dates and, hence, the date
test in my suggested procedure failed.

Once the dates issue was resolved, my procedure ran without problem.

I have, therefore, added some initial code to convert column M to
recognisable dates.

I have also added a function to verify that the correct sheet is open.

The updated code is:

'===============================>>
Public Sub DeleteDataRows()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Dim Rng As Range
Dim Rng1 As Range
Const shtName As String = "S2661060" '<<=== CHANGE??

On Error GoTo XIT

If Not SheetExists(shtName) Then
MsgBox "No " & shtName & " S2661060 sheet found" _
& vbNewLine & _
"Check that correct workbook is active!", _
vbCritical, _
"Check Workbook"
Exit Sub
End If

Set sh = Sheets(shtName)

With sh
Set Rng1 = Intersect(.UsedRange, .Columns("M"))
End With

Set Rng1 = Rng1.Offset(1).Resize(Rng1.Rows.Count - 1, 1)

Application.ScreenUpdating = False

With Rng1
.Value = .Value
.NumberFormat = "mmm-dd-yy"
End With

Lrow = Cells(Rows.Count, "K").End(xlUp).Row

For i = Lrow To 2 Step -1
Set Rng = Range("K" & i)
If Not IsEmpty(Rng1) Then
If Rng.Value <> "0000" Then
If Rng.Offset(0, 2).Value > Date - 7 Then
' Rng.EntireRow.Delete '<<=== REINSTATE
Rng(1, 2).Interior.ColorIndex = 36 '<<==DELETE
End If
End If
End If
Next

XIT:
Application.ScreenUpdating = True

End Sub
'<<===============================
'===============================>>
Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ActiveWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function
'<<===============================


The procedure is written to allow you readily to satisfy yourself that it
does what you want and expect.

So, rather than deleting rows, the corresponding column M values are
highlighted.

Once you are happy that everthing is as it should be, delete the line:

rng(1, 2).Interior.ColorIndex = 36

and uncomment the prededing line:

Rng.EntireRow.Delete

This done, save the file, and copy/paste the code (including the function)
to your Personal.xls.

Finally, to check the code with the marked data you sent me, you will need
either to:

Turm your clock back by four days, or

Change (temporarily) the date condition of Date - 7 to
Date - 3

In any event, restore the clock or date condition!

---
Regards,
Norman



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> I just emailed it to you Norman. Thanks for taking the time to look at
> it.
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[Norman Jones]

Hi DKY,

I received your workbook.

The data in column M on Sheet S2661060 are not dates and, hence, the date
test in my suggested procedure failed.

Once the dates issue was resolved, my procedure ran without problem.

I have, therefore, added some initial code to convert column M to
recognisable dates.

I have also added a function to verify that the correct sheet is open.

The updated code is:

'===============================>>
Public Sub DeleteDataRows()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Dim Rng As Range
Dim Rng1 As Range
Const shtName As String = "S2661060" '<<=== CHANGE??

On Error GoTo XIT

If Not SheetExists(shtName) Then
MsgBox "No " & shtName & " S2661060 sheet found" _
& vbNewLine & _
"Check that correct workbook is active!", _
vbCritical, _
"Check Workbook"
Exit Sub
End If

Set sh = Sheets(shtName)

With sh
Set Rng1 = Intersect(.UsedRange, .Columns("M"))
End With

Set Rng1 = Rng1.Offset(1).Resize(Rng1.Rows.Count - 1, 1)

Application.ScreenUpdating = False

With Rng1
.Value = .Value
.NumberFormat = "mmm-dd-yy"
End With

Lrow = Cells(Rows.Count, "K").End(xlUp).Row

For i = Lrow To 2 Step -1
Set Rng = Range("K" & i)
If Not IsEmpty(Rng1) Then
If Rng.Value <> "0000" Then
If Rng.Offset(0, 2).Value > Date - 7 Then
' Rng.EntireRow.Delete '<<=== REINSTATE
Rng(1, 2).Interior.ColorIndex = 36 '<<==DELETE
End If
End If
End If
Next

XIT:
Application.ScreenUpdating = True

End Sub
'<<===============================
'===============================>>
Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ActiveWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function
'<<===============================


The procedure is written to allow you readily to satisfy yourself that it
does what you want and expect.

So, rather than deleting rows, the corresponding column M values are
highlighted.

Once you are happy that everthing is as it should be, delete the line:

rng(1, 2).Interior.ColorIndex = 36

and uncomment the prededing line:

Rng.EntireRow.Delete

This done, save the file, and copy/paste the code (including the function)
to your Personal.xls.

Finally, to check the code with the marked data you sent me, you will need
either to:

Turm your clock back by four days, or

Change (temporarily) the date condition of Date - 7 to
Date - 3

In any event, restore the clock or date condition!

---
Regards,
Norman


"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> I just emailed it to you Norman. Thanks for taking the time to look at
> it.
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[DKY]

I copied and pasted this into my personal workbook and ran the macro. I changed my date on my pc to the 14th of July and it highlights anything with a value in K. No matter what the date is in M. Its set to highlight anything with a date > Date - 7 in column M, (which according to my calculations is any date that's greater than July 7th. Problem is it still highlights those whose dates are July 5th. That's not greater, that's less than. Does it highlight those with those dates on your end when you run it? Or am I doing something wrong??

[DKY]

I'm sorry, I copied and pasted from the first post. There must be an edit in the code and that's why there's two posts. The second one seems to work fine. I'll test it out more this morning when I get to work. Thanks Norm

[Norman Jones]

Hi DKY,

The code runs without problem for me using your copy workbook.

If you want me to send a working version of your workbook, mail me.

---
Regards,
Norman



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> I'm sorry, I copied and pasted from the first post. There must be an
> edit in the code and that's why there's two posts. The second one
> seems to work fine. I'll test it out more this morning when I get to
> work. Thanks Norm
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=387731
>

[DKY]

The code runs flawless, thank you very much Norman. I had copied the wrong code above. My mistake, thanks again.