macro to colour empty cells (cells not recognized as empty)

[Gerben]

Hello,

I have a problem marking cells on a worksheet,
the macro I made should colour the cell if it is empty or has a valu
1.

It only works for the cells that contain value 1, not for the empt
ones.
Probably it has to do with the fact that the all cells (including th
empty ones) are copied from other sheets,
and are not really seen as empty by the macro.

Here the Macro:

counter = 1
While counter <= 5
counter2 = 1
While counter2 <= 542
If ActiveCell = " " Then
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If

If ActiveCell.Value <= 1 Then
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
counter2 = counter2 + 1
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Wend
counter = counter + 1
ActiveCell.Offset(rowOffset:=-542, columnOffset:=1).Activate
Wend

Can anybody help me with this issue?
Thanks in advance,
Gerbe

 

[Roman]

Hi Gerben,
the thing is that empty cell value is not " " but "".

Sub recol()
For Each ccell In Selection
If ccell.Value = "" Then GoSub colorit
If ccell.Value = 1 Then GoSub colorit
Next
Exit Sub
colorit:
ccell.Interior.ColorIndex = 20
Return
End Sub

 

[Gerben]

Hi Roman,

the thing is that empty cell value is not " " but "" doesn't matter, as
i tried.

there should be something with the fact that the empty cells are copied
empty cells,

Any ideas?

 

[Roman]

Hi Gerben,
are you sure the cells are really empty? Copying empty cells should not
affect anything. Empty cells are empty cells no matter where and how
many times copyied.
Check if there are not blank spaces in your cells as " " and "" is a
big difference.

 

[Gerben]

Roman

it had to be " 3spacebars " instead of "2spacebars "

The small things make the big differences

 

[Roman]

It certainly does!
It also means that the cells were not empty but contained three spaces.