If I want the cells with I:

If cell = "I" Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8

So How do I refer to Empty cells?

If cell = *EMPTY* Then cell.Offset(0, 2).Interior.Pattern =
xlPatternGray8


--
Turquoise_dax
------------------------------------------------------------------------
Turquoise_dax's Profile: http://www.excelforum.com/member.php...o&userid=35185
View this thread: http://www.excelforum.com/showthread...hreadid=554622

I tend to use

if len(cell.value)=0 then CellIsEmpty

But I'm sure it's not the only way!!!!

Turquoise_dax wrote:
If I want the cells with I:

If cell = "I" Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8

So How do I refer to Empty cells?

If cell = *EMPTY* Then cell.Offset(0, 2).Interior.Pattern =
xlPatternGray8


--
Turquoise_dax
------------------------------------------------------------------------
Turquoise_dax's Profile: http://www.excelforum.com/member.php...o&userid=35185
View this thread: http://www.excelforum.com/showthread...hreadid=554622

Try ...

If cell = "" Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8

Try IsEmpty() function:

if IsEmpty(Range("A1")) then

HTH
--
AP

"Turquoise_dax"
a écrit dans le message de news:
...

If I want the cells with I:

If cell = "I" Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8

So How do I refer to Empty cells?

If cell = *EMPTY* Then cell.Offset(0, 2).Interior.Pattern =
xlPatternGray8


--
Turquoise_dax
------------------------------------------------------------------------
Turquoise_dax's Profile:
http://www.excelforum.com/member.php...o&userid=35185
View this thread: http://www.excelforum.com/showthread...hreadid=554622

I should have put all of it...

Dim cell As Range
For Each cell In Range("B:B")
If cell= *???* Then cell.Offset(0, 1).Interior.Pattern =
xlPatternGray8
Next

I tried "", not working...


--
Turquoise_dax
------------------------------------------------------------------------
Turquoise_dax's Profile: http://www.excelforum.com/member.php...o&userid=35185
View this thread: http://www.excelforum.com/showthread...hreadid=554622

If IsEmpty(cell.Value) Then

or

If cell.Value = "" Then

or

If Len(cell.Value) = 0 Then

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Turquoise_dax"
wrote in message
news:Turquoise_dax.29t99r_1150991807.572@excelforu m-nospam.com...

If I want the cells with I:

If cell = "I" Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8

So How do I refer to Empty cells?

If cell = *EMPTY* Then cell.Offset(0, 2).Interior.Pattern =
xlPatternGray8


--
Turquoise_dax
------------------------------------------------------------------------
Turquoise_dax's Profile:
http://www.excelforum.com/member.php...o&userid=35185
View this thread: http://www.excelforum.com/showthread...hreadid=554622

On Thu, 22 Jun 2006 11:14:57 -0500, Turquoise_dax wrote:

I should have put all of it...

Dim cell As Range
For Each cell In Range("B:B")
If cell= *???* Then cell.Offset(0, 1).Interior.Pattern =
xlPatternGray8
Next

I tried "", not working...

Maybe you have some garbage in the cells, so they look as empty but are NOT
empty.

Try the Trim() function:

If Trim(cell.value)= "" Then
cell.Offset(0, 1).Interior.Pattern=xlPatternGray8
End If

--
PL

I did
n(cell.Value) = 0 Then cell.Offset(0, 1).Interior.Pattern =
xlPatternGray8

It works, but the program is jamming for a long time, often I get the
"not responding messge"

If I get rid of this line, the code works just fine....
(Any idea why excel has trouble processing this one?)


--
Turquoise_dax
------------------------------------------------------------------------
Turquoise_dax's Profile: http://www.excelforum.com/member.php...o&userid=35185
View this thread: http://www.excelforum.com/showthread...hreadid=554622

Great, another problem came up....

Is there something I can add to my program so that Excel applies my
conditionnal formatting (for empty cell) until the end of my file only
(and not to the 65536th row....)


--
Turquoise_dax
------------------------------------------------------------------------
Turquoise_dax's Profile: http://www.excelforum.com/member.php...o&userid=35185
View this thread: http://www.excelforum.com/showthread...hreadid=554622

On Thu, 22 Jun 2006 11:41:02 -0500, Turquoise_dax wrote:

I did
n(cell.Value) = 0 Then cell.Offset(0, 1).Interior.Pattern =
xlPatternGray8

It works, but the program is jamming for a long time, often I get the
"not responding messge"

If I get rid of this line, the code works just fine....
(Any idea why excel has trouble processing this one?)

Formatting multiple cells this way is very inefficient for large amountsd
of data. If you want to get rid of "Not reponding" behaviour, execute
DoEvents every 10 or 100 iterations.
This won't speed up the process (in fact, it will slow down it).

If you want to go REALLY fast, try to:

- insert a column
- put consecutive numbers to that column (to store the original order of
rows)
- sort table using the "conditional" column
- format ALL the neccessary cells in ONE instruction (well, you'll have to
find the first and the last cell in a block)
- sort the table back to get the original order
- remove the "sorting" column

This works fine for me, I have really huge tables to format (more that 20k
rows, more than 20 cols) and multiple autoformatting using this method
takes less than a second.

--
PL