I coded as this below.
When I run DemoUsedRange, no problem!
But when I add CellNumRandum, the error occured.(Application-defined or
object-defined error)
Please give me some comments.

Thanks.


' My Code

Option Explicit

Sub DemoUsedRange()
Dim cel As Range
Dim str As String

For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then str = str & cel.Address & ","
Next

ActiveSheet.Range(Left(str, Len(str) - 1)).Select ' Error
Selection.Font.ColorIndex = 3
End Sub

Sub CellNumRandom()
' Range("A1:J24").Select
Dim cel As Range
For Each cel In ActiveSheet.UsedRange

Randomize

Dim random As Long
random = Int((100 - (-100) * Rnd) - 100)
If random < 30 Then random = -random

cel.Value = random
Next cel
DemoUsedRange ' call upper procedure
End Sub

Your string is probably too long.

Sub DemoUsedRange()
Dim cel As Range
Dim str As String
Dim rng as range
For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then
if rng is nothing then
set rng = cel
else
set rng = union(rng,cel)
end if
End if
Next
if not rng is nothing then
rng.ColorIndex = 3
' if you want to select
rng.Select
end if
End Sub

or

Sub DemoUsedRange()
Dim cel As Range
Dim str As String
For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then cell.Interior.ColorIndex = 3
Next
End Sub


--
Regards,
Tom Ogilvy


wrote in message
ups.com...
I coded as this below.
When I run DemoUsedRange, no problem!
But when I add CellNumRandum, the error occured.(Application-defined or
object-defined error)
Please give me some comments.

Thanks.


' My Code

Option Explicit

Sub DemoUsedRange()
Dim cel As Range
Dim str As String

For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then str = str & cel.Address & ","
Next

ActiveSheet.Range(Left(str, Len(str) - 1)).Select ' Error
Selection.Font.ColorIndex = 3
End Sub

Sub CellNumRandom()
' Range("A1:J24").Select
Dim cel As Range
For Each cel In ActiveSheet.UsedRange

Randomize

Dim random As Long
random = Int((100 - (-100) * Rnd) - 100)
If random < 30 Then random = -random

cel.Value = random
Next cel
DemoUsedRange ' call upper procedure
End Sub

Many thanks, Ogilvy!




Tom Ogilvy ÀÛ¼º:

Your string is probably too long.

Sub DemoUsedRange()
Dim cel As Range
Dim str As String
Dim rng as range
For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then
if rng is nothing then
set rng = cel
else
set rng = union(rng,cel)
end if
End if
Next
if not rng is nothing then
rng.ColorIndex = 3
' if you want to select
rng.Select
end if
End Sub

or

Sub DemoUsedRange()
Dim cel As Range
Dim str As String
For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then cell.Interior.ColorIndex = 3
Next
End Sub


--
Regards,
Tom Ogilvy


wrote in message
ups.com...
I coded as this below.
When I run DemoUsedRange, no problem!
But when I add CellNumRandum, the error occured.(Application-defined or
object-defined error)
Please give me some comments.

Thanks.


' My Code

Option Explicit

Sub DemoUsedRange()
Dim cel As Range
Dim str As String

For Each cel In ActiveSheet.UsedRange
If cel.Value < 0 Then str = str & cel.Address & ","
Next

ActiveSheet.Range(Left(str, Len(str) - 1)).Select ' Error
Selection.Font.ColorIndex = 3
End Sub

Sub CellNumRandom()
' Range("A1:J24").Select
Dim cel As Range
For Each cel In ActiveSheet.UsedRange

Randomize

Dim random As Long
random = Int((100 - (-100) * Rnd) - 100)
If random < 30 Then random = -random

cel.Value = random
Next cel
DemoUsedRange ' call upper procedure
End Sub