What Is Wrong Here?

[SkippyPB]

I have the following VBA code in an Excel 2003 spreadsheet. It is
part of other code but by using Debug I have found that everything
before it executes alright. It is just this code that is not doing
its job. It is:

If Target.Column = 4 Then
Ecell = "N"
nRow = Target.Row
For nCol = 1 To 4
If IsEmpty(Worksheets("ITR Setup").Cells(nRow, nCol)) Then
Worksheets("ITR Setup").Cells(nRow, nCol).Interior.ColorIndex =
vbYellow
Ecell = "Y"
End If
Next nCol
If Ecell = "Y" Then
MsgBox "Highlighted Cells Must Contain Data"
End If
End If

By using Debug I see it execute the If IsEmpty statement. It should
be found True in my testing but the next line executed is the last End
If. It doesn't go to the Next Ncol statement.

I'd appreciate any help here.

thanks.
////
(o o)
-oOO--(_)--OOo-

"I'm not afraid of dying. I just don't want to
be there when it happens."
--Woody Allen
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[Jim Thomlinson]

Try this...

If target.Column = 4 Then
Ecell = "N"
nRow = target.Row
For nCol = 1 To 4
With Worksheets("ITR Setup").Cells(nRow, nCol)
If Trim(.Value) = "" Then
.Interior.ColorIndex = vbYellow
Ecell = "Y"
End If
end with
Next nCol
If Ecell = "Y" Then
MsgBox "Highlighted Cells Must Contain Data"
End If
End If

 

[stjori]

I have the following VBA code in an Excel 2003 spreadsheet. It is
part of other code but by using Debug I have found that everything
before it executes alright. It is just this code that is not doing
its job. It is:

If Target.Column = 4 Then
Ecell = "N"
nRow = Target.Row
For nCol = 1 To 4
If IsEmpty(Worksheets("ITR Setup").Cells(nRow, nCol)) Then
Worksheets("ITR Setup").Cells(nRow, nCol).Interior.ColorIndex =
vbYellow
Ecell = "Y"
End If
Next nCol
If Ecell = "Y" Then
MsgBox "Highlighted Cells Must Contain Data"
End If
End If

By using Debug I see it execute the If IsEmpty statement. It should
be found True in my testing but the next line executed is the last End
If. It doesn't go to the Next Ncol statement.

I'd appreciate any help here.

thanks.
////
(o o)
-oOO--(_)--OOo-

"I'm not afraid of dying. I just don't want to
be there when it happens."
--Woody Allen
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

Is there a formulae in the cell on which you're doing the IsEmpty
check? Are you sure it's empty. Otherwise hard to say without seeing
the file.
Is "If IsEmpty(Worksheets("ITR Setup").Cells(nRow, nCol))" a typo?
Should be If(IsEmpty(Worksheets("ITR Setup").Cells(nRow, nCol)))

 

[Tom Ogilvy]

Think you have a typo

.Interior.ColorIndex = vbYellow


should probably either be

.Interior.Color = vbYellow
or

.Interior.ColorIndex = 6

 

[Bill Renaud]

Excel Help suggests using the RGB function to generate the color, if you
use the Color property instead of ColorIndex:

Worksheets("ITR Setup").Cells(nRow, nCol).Interior.Color = RGB(255, 255,
0)

 

[SkippyPB]

Try this...

If target.Column = 4 Then
Ecell = "N"
nRow = target.Row
For nCol = 1 To 4
With Worksheets("ITR Setup").Cells(nRow, nCol)
If Trim(.Value) = "" Then
.Interior.ColorIndex = vbYellow
Ecell = "Y"
End If
end with
Next nCol
If Ecell = "Y" Then
MsgBox "Highlighted Cells Must Contain Data"
End If
End If

This did not work either. It appears that, even though Help suggests
using vbYellow, it doesn't like that Interior.ColorIndex statement at
all. Debugging shows it is jumping out on that statement in both your
version and mine.

See my response further down in this thread.

Regards,
////
(o o)
-oOO--(_)--OOo-

"I'm not afraid of dying. I just don't want to
be there when it happens."
--Woody Allen
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[SkippyPB]

Is there a formulae in the cell on which you're doing the IsEmpty
check? Are you sure it's empty. Otherwise hard to say without seeing
the file.

There are no formulas and I'm absolutely certain the cell is empty
since I'm doing the testing.

Is "If IsEmpty(Worksheets("ITR Setup").Cells(nRow, nCol))" a typo?
Should be If(IsEmpty(Worksheets("ITR Setup").Cells(nRow, nCol)))

No typo. IsEmpty(.Value) is the proper syntax and it doesn't need to
be in a parenthetical statement in this case.

Thanks for responding.

////
(o o)
-oOO--(_)--OOo-

"I'm not afraid of dying. I just don't want to
be there when it happens."
--Woody Allen
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[SkippyPB]

Think you have a typo

.Interior.ColorIndex = vbYellow


should probably either be

.Interior.Color = vbYellow
or

.Interior.ColorIndex = 6

Thanks Tom. That was the problem. Bob Renaud's suggestion of using
RGB worked as well.

I put a statement in the code so that the Cell is set to white before
it goes into the edits. I noticed that when it changes a yellow cell
back to white, the grid lines disappear. Any idea how to get them
back to preserve them?

Thanks.

////
(o o)
-oOO--(_)--OOo-

"I'm not afraid of dying. I just don't want to
be there when it happens."
--Woody Allen
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[SkippyPB]

Thanks Tom. That was the problem. Bob Renaud's suggestion of using
RGB worked as well.

I put a statement in the code so that the Cell is set to white before
it goes into the edits. I noticed that when it changes a yellow cell
back to white, the grid lines disappear. Any idea how to get them
back to preserve them?

Thanks.
Remove nospam to email me.

Steve

OK nevermind! I figured this out. I changed to using
Interior.ColorIndex = 36
I added
Interior.Pattern = xlSolid

At the beginning of the code before any IFs are run I added:

nRow = Target.Row
nCol = Target.Column

Worksheets("ITR Setup").Cells(nRow, nCol).Interior.ColorIndex = xlNone

This works much better. Thanks for all of the help.

Regards,
////
(o o)
-oOO--(_)--OOo-

"I'm not afraid of dying. I just don't want to
be there when it happens."
--Woody Allen
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[Bill Renaud]

Remember that when you use

..Interior.ColorIndex = 36

you are specifying the index value into the current color palette
(however it is currently set on the local machine). On your system, this
may be yellow, but if this same macro is run on another machine where
the user has changed the color for this palette index value to be
orange, for example, then your cell will be colored orange.

I believe that using the RGB(red, green, blue) function will specify the
color you want, regardless of local machine settings.

Just something to remember.