excel - Test for Null in IF statement

Question

I got the sub below from: Format cell based on formula value from "JosieP"

I am trying to test for Null cells as the sub fails if it encounters one. If it encounters a null cell I want to add a color to the cell

If IsNull(rCell) Then rCell.Interior.Color = 8 does not work but does not fail either.

if clng(Left(Right(rcell.value, 2), 1)) < 3 Then rcell.Interior.ColorIndex = 10 fails when there is a null cell .

I tried adding Not IsNull(rCell) so I would have if clng(Left(Right(rcell.value, 2), 1)) < 3 And Not IsNull(rCell) Then rcell.Interior.ColorIndex = 10 but this fails as well.

Sub Format()

Dim LastRow As Long
Dim WS As Worksheet
dim rCell as range

Set WS = Sheets("sheet1")

LastRow = WS.range("F" & WS.Rows.Count).End(xlUp).Row

for each rcell in WS.range("F2:F" & LastRow).cells

 If IsNull(rCell) Then rCell.Interior.Color = 8

if clng(Left(Right(rcell.value, 2), 1)) < 3 And Not IsNull(rCell) Then rcell.Interior.ColorIndex = 10

next rcell
End Sub

Answer 1

Cell values in Excel never contain the Null value. If the cell is blank, it is Empty.

If IsEmpty(rCell.Value) Then ...

Also, a cell that is blank is different from a cell that contains an empty string (zero-length string). To test for those, you use

If Len(rCell.Value) > 0 Then ...

Note that unlike IsEmpty, this is not protected from a type mismatch error. If your cell happens to contain an error value (e.g. #N/A), you will get a runtime error 13: Type mismatch when trying to check its length. To be extra safe, you may want to check for IsError(rCell.Value) first.