0

I wonder if anyone can help I am using a macro but I need to adapt it ...

 Sub findApp()
 lookfor = Selection.Value
 Sheets("Home (2)").Activate
 Cells.Find(What:=lookfor, After:=ActiveCell, 
 LookIn:=xlValues, _
 LookAt:=xlWhole, SearchOrder:=xlByRows, 
 SearchDirection:=xlNext, _
 MatchCase:=False).Select

If todays date is >or equal to January but < July search column “D”sheet2 If todays date is >or equal to July but <January search column “J” sheet2 I would really appreciate some help Thanks

PAUL
  • 1

1 Answers1

0

This code will search column D for today's date + 6 months if the month is between Jan and June, or column J if month is between July and December and color every match green.

Not 100% sure that is what you are after but please clarify if not.

Sub findApp()
    Dim rw As Integer
     
     With Sheet2
        If Month(Date) < 7 Then
            For rw = 2 To .Range("D1048576").End(xlUp).Row
                If .Cells(rw, 4).Value = DateAdd("m", 6, Date) Then
                    .Cells(rw, 4).Interior.ColorIndex = 4
                End If
            Next rw
        Else
            For rw = 2 To .Range("J1048576").End(xlUp).Row
                If .Cells(rw, 10).Value = DateAdd("m", 6, Date) Then
                    .Cells(rw, 10).Interior.ColorIndex = 5
                End If
            Next rw
        End If
     End With
End Sub
Jeremy Hodge
  • 612
  • 3
  • 14
  • Side note (for this answer and future ones): [Use `Long` instead of `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). – BigBen Apr 29 '21 at 21:00
  • Also: `.Range("D1048576")` ----> `.Range("D" & .Rows.Count)` and similarly for `.Range("J1048576")`. Old versions of Excel, e.g. 2003, have fewer rows. – BigBen Apr 29 '21 at 21:01
  • Thank you so much I thought that was what I wanted but what I really want is something like this I have two named ranges rand _1 and range_2 here is the code but its not quite right – PAUL May 08 '21 at 18:16