simple: type mismatch

feel like an idiot that i can't get this one right... never-the-less...

B3:B10 have no special format, and could either have text, or be null

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, ActiveSheet.Range("B3:B10")) Is Nothing Then
If Len(trim(Range(Target.Address).Value) )= 0 Then
Target.Interior.ColorIndex = 53
Else
Target.Interior.ColorIndex = 36
End If
End If
End Sub

run time error 13
type mismatch

thanks in advance,
mark

Mark,

Not sure exactly what you are trying to do, but;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ranRange As Range

For Each ranRange In Target.Cells

If Not Intersect(ranRange, ActiveSheet.Range("B3:B10")) Is Nothing
Then
If Len(Trim(Range(ranRange.Address).Value)) = 0 Then
Target.Interior.ColorIndex = 53
Else
Target.Interior.ColorIndex = 36
End If
End If

Next ranRange

End Sub

Gives no error ... the important bit I guess is that when you do the 'Trim'
the range is one cell (which the for each ensures).

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"mark kubicki" wrote:

> feel like an idiot that i can't get this one right... never-the-less...
>
> B3:B10 have no special format, and could either have text, or be null
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Not Intersect(Target, ActiveSheet.Range("B3:B10")) Is Nothing Then
> If Len(trim(Range(Target.Address).Value) )= 0 Then
> Target.Interior.ColorIndex = 53
> Else
> Target.Interior.ColorIndex = 36
> End If
> End If
> End Sub
>
> run time error 13
> type mismatch
>
> thanks in advance,
> mark
>
>
>