On Saturday, March 9, 2013 11:50:20 PM UTC-5, Howard wrote:
On Saturday, March 9, 2013 7:34:36 PM UTC-8, Wes Finch wrote:

Hi,



I need some help with this macro. When I run it I get the yellow explanation mark on some cells in column D which I formatted as text. It says a date string is represented with only two digits for the year. This is not date information but pure text. How to I tell Excel 2003 that column D is not a date?



Thanks,



Wes







Basically you enter numbers into column A and run the macro. This highest number is recorded in B and the lowest in C with a concatenation in D of B/C. Enter new values in A and run it again etc.







Sub HiLo()



Dim LastRow As Long, i As Long



With ActiveSheet



LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row



For i = 1 To LastRow



' if cells A < B (or B empty) then B = A (highest value)



If .Range("A" & i) < .Range("B" & i) Or IsEmpty(.Range("B" & i)) Then



.Range("B" & i) = .Range("A" & i).Value



End If



' if cells A B then B = A (lowest value)



If .Range("A" & i) .Range("C" & i) Then



.Range("C" & i) = .Range("A" & i).Value



End If



'concatenate cells B & C into D



.Range("D" & i) = .Range("B" & i) & "/" & .Range("C" & i)



Next



End With



End Sub





Give this a try.



.Range("D" & i) = "'" & .Range("B" & i) & "/" & .Range("C" & i)



Regards,

Howard

Sorry Howard I did not test it hard enough. In some instances it is still reverting to a date.

Wes