help with last part of script

[RompStar]

Rng.AutoFilter Field:=1, Criteria1:=">=" & CLng(BeginDate),
Operator:=xlAnd, Criteria2:="<=" & CLng(EndDate)

Set Rng = Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1, 1)
Set Rng = Rng.SpecialCells(xlCellTypeVisible)
Rng.Offset(0, 4).FormulaR1C1 = "=NETWORKDAYS(RC[-4],RC[-3])-1"
Rng.AutoFilter

' Do Number coloring as a Visual Step...

Set c = Range("N11:N" & LastRow)
For Each Item In c
If Val(Item) > 2 Or Val(Item) < 0 Then
Item.Font.Bold = True
Item.Font.COLOR = vbRed
Else
Item.Font.Bold = True
Item.Font.COLOR = vbBlack
End If
If Val(Item) < -20000 Then
Item.Value = NA
End If
Next Item


The networkdays is prefilled into Column N based on the range N11: last
row, then the formula colculates differences between dates and the If
Val, highlights any numbers that are <0 in Red, > 2 in Red, 0, 1, 2 as
Black.

That works

some cells in column N have large negative values because the column
that contain the dates for comparison J and K, if a date is missing in
K, it spits out a large nagative value in the formula like -27000, I
want anything that's greater then -20000 for that number to be replaced
by the workds N/A, and the last part isn't working, what did I do wrong
?

 

[Jim Cone]

Hello again,

Maybe one of these...

Item.Value = "NA"
Item.Value = "#N/A"
Item.Formula = "=NA()"

Also, "Item" is a property and a method in VBA depending on
where or how it is used. It is not best practice to use it as a variable.

Jim Cone
San Francisco, USA

 

[RompStar]

probably not the best way, but this works :- )

Set c = Range("N11:N" & LastRow)
For Each Item In c
If Val(Item) > 2 Or Val(Item) < 0 Then
Item.Font.Bold = True
Item.Font.COLOR = vbRed
Else
Item.Font.Bold = True
Item.Font.COLOR = vbBlack
End If
Next Item

Set c = Range("N11:N" & Range("N65536").End(xlUp).Row)
For Each Item In c
If Val(Item) < -20000 Or Val(Item) > 20000 Then
Item.Value = "Missing Date!!!"
Else
End If
Next Item

Finish:
End Sub