Excel Column Value

[scorpion53061]

Here is what I am trying to do........forgive the word problem format as it
seems the best way I can explain it.

I want to check the value of each cell in a column (it would be the first
column) IF the column has "DATEOR" for the "A1" value. If it does not we are
going to skip this block entirely.

If the value of the items in the column except A1 are older than 90 days
from Date.Now than the back color of the cell should be yellow. If not it
should remain white.

Now I think I know why it does not work for me - I keep getting invalid cast
exceptions and I think it is because the cell is of the type "General". But
if i try to select it and turn it into a date format when I run the code
Office 2000 will not agree.

Any ideas would be great. And Thank You!!

 

[Tom Ogilvy]

You are making reference to your specific code, but don't show the code or
where the error is occuring in the code.

 

[scorpion53061]

Sorry Tom,

Well I didnt post it because I knew it was not right and I didnt want to
embarress myself. But in doing so I probably gave the impression I have not
made an effort.....but if you can help I am willing to embarress myself I
guess

timeofrecord is another variable decalred earlier which is the time of the
server it is sitting on.

Here is what I have....
Dim p as Integer
For p = 1 To WSheet.Rows.Count
Dim ObjVal As Object
ObjVal = WSheet.Cells(p, 1)
Dim days As Integer
Dim cellValue As String
cellValue = CType(ObjVal, String)
days = DateDiff(DateInterval.Day, CDate(cellValue),
CDate(timeofrecord))
If days >= 90 Then
With WSheet.Cells(p, 1).Selection.Interior
.ColorIndex = 6
.Pattern = 1 'xlSolid
.PatternColorIndex = 4105 'xlAutomatic
End With
WSheet.Cells(p, 1).Selection.Interior.ColorIndex = 6
End If

Next
End If

 

[scorpion53061]

Tom,

I believe I have resolved this issue.

I need to test it with Office 2000 yet (I use Office 2003) but I think it
will work.

Thank you for your time.

 

[Tom Ogilvy]

When assigning something to an object/range, you use Set

Dim p as Long
Dim ObjVal As Range
Dim days As Long
Dim cellValue As Date
Dim timeofrecord As Date
Dim WSheet as Worksheet
Dim rng as Range, cell as Range
TimeofRecord = Date ' today's date
set WSheet = Worksheets("Data")
with WSheet
set rng = .Range(.Cells(1,1),Cells(1,256).End(xltoLeft))
End with
for each cell in rng
if lcase(rng.value) <> "dateor" then
For p = 1 To WSheet.Rows.Count
set ObjVal = WSheet.Cells(p, 1)
if isdate(objval) then
cellValue = objval.Value
days = clng(TimeofRecord - cellValue)
If days >= 90 Then
With WSheet.Cells(p, 1).Interior
.ColorIndex = 6
.Pattern = 1 'xlSolid
.PatternColorIndex = 4105 'xlAutomatic
End With
else
WSheet.Cells(p, 1).Selection.Interior.ColorIndex = xlNone
End if
End if
Next
End if
Next

I wanted to see your code because I was not sure of your description. Plus
it is better to suggest changes than to provide a completely different
approach.

--
regards,
Tom Ogilvy