What Is Wrong Here?

  • Thread starter Thread starter SkippyPB
  • Start date Start date
S

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
 
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
 
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
Click to expand...

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)))
 
Think you have a typo

.Interior.ColorIndex = vbYellow


should probably either be

.Interior.Color = vbYellow
or

.Interior.ColorIndex = 6
 
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)
 
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
Click to expand...

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
 
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.
Click to expand...

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)))
Click to expand...

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
 
Think you have a typo

.Interior.ColorIndex = vbYellow


should probably either be

.Interior.Color = vbYellow
or

.Interior.ColorIndex = 6
Click to expand...

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
 
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
Click to expand...

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
 
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.
 
Back
Top