how to detect Space inside Excel cell VBA Excel

The code i have below works fine if the cell is empty (has had nothing entered into it and has not been editted):



If Range(sRange).Text <> "" Then
   Range(sRange).Interior.ColorIndex = iColour
End If



But if there is a 'SPACE' (i.e. someone has pressed the spacebar inside the cell) or multiple spaces inside the cell then it colours it as it sees it as NOT EMPTY. How can i get around this? What code can i add into my if statement that i have posted above?

Thanks.

Picco
crmpiccoAsked:
Who is Participating?
 
DreamMasterCommented:
If Trim(Range(sRange).Text) <> "" Then
   Range(sRange).Interior.ColorIndex = iColour
End If

This should work as well...

Regards,
Max.
0
 
SnowFlakeCommented:
Hi crmpicco,

If length(trim(Range(sRange).Text))<>0 Then
    Range(sRange).Interior.ColorIndex = iColour
End If



SnowFlake
0
 
tonym001Commented:
You could use the replace function
strRange = Replace(Range(sRange).Text, " ", "")
if strRange = "" then
    Range(sRange).Interior.ColorIndex = iColour
End If
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
DreamMasterCommented:
Uhh...tony...I might be mistaking...but don't you mean:

if strRange <> "" then
   Range(sRange).Interior.ColorIndex = iColour
End if

Regards,
Max.
0
 
tonym001Commented:
yep sorry but you get the idea
0
 
DreamMasterCommented:
Sure I do...just don't get why you'd replace spaces when you can do it with trim...which is meant for trimming spaces...

Regards,
Max.
0
 
SnowFlakeCommented:
crmpicc,
do you still need more assistance with this ?
0
 
tonym001Commented:
max agreed - but users are users and i trust them about as far as I can kick them.
0
 
tonym001Commented:
this question is over n I think dreammaster has the points - and I am a bit pissed so leave me alone
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.