help with last part of script

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
?

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" <rmiecznik@comcast.net> wrote in message
news:1114102945.431703.311140@g14g2000cwa.googlegroups.com...
> 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?

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