Solved: Finding Matching Date

[zoom38]

Hello all I am trying to find a date that is enterred into Q26 within the range C5:AD5. Below is the code I've been trying but it doesn't work.


Sub BoldMeetingDate()
Dim MeetingDate As Date
Dim c As Range
Dim FoundIt
Dim FirstAddress

'Find The Member To Be Deleted
MeetingDate = Range("Q26").Value

With ActiveSheet.Range(Cells(5, 3), Cells(5, 30))
Set c = Cells.Find(What:=(MeetingDate), _
LookAt:=xlWhole, LookIn:=xlValues, searchorder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)
End With



If Not c Is Nothing Then
FoundIt = c.Address
End If


End Sub



Ultimately I want to enter the date in Q26, find it in the Range C5:AD5 and hilight the column where it is found. Attached is a slim version of my spreadsheet. Thanks in advance for your help.

Gary

[mdmackillop]

I've added the dates in Row 25 in a searchable form. Once found you can offset to highlight to desired range.

I've also changed Merged cells to Centre Across Selection, and formatted them to use real dates rather than formatted text.

[zoom38]

thanks MD, I assume you mean you changed the dates in row 5 into a searchable form. Now does this mean that I can't do it in the format that I had them? I had them that way only because in order to change the date in R1 to all capital letters I coded it to text, see below.



If Not Intersect(Target, Me.[r1]) Is Nothing Then
Me.[r1].Formula = "=""" & UCase(Format(Me.[r1], "mmmm d, yyyy")) & """"
End If



Because of this I had to use DateValue in Row 5. Is there another way to capitalize the date in r1 so I can use your method in row 5?

Thanks Again
Gary

[mdmackillop]

The dates are in Row 25 as stated.

[mdmackillop]

R1 here is formatted as Text and the dated typed in upper case. DataValue is used to get the date for your table.

[zoom38]

Thanks MD,

Can you explain why if I narrow the columns so that the dates in Row 25 are not visible ##### it does not work. Quite curious, the values do not change, just the way its shown is changed and because of this the routine does not work. In order for it to print out the way I want it I need to change the column width of columns C thru AD to 4.71.

Thanks
Gary

[mdmackillop]

As it seems to relate to the cell display, try changing Row 25 font size to 1

[zoom38]

Of course I did it the hard way by creating code that widens the column and then returns it to the proper with.

Thanks
Problem solved
Gary