color index

[lior03]

hello
i wanted to get both a cell's font colornumber as well as interior colornumber. i came out with this:


Sub findcolorfont()
Dim x As Integer
Dim y As Integer
If IsEmpty(ActiveCell) Then GoTo errhandler
x = ActiveCell.Font.ColorIndex
y = ActiveCell.Interior.ColorIndex
MsgBox "activecell font colornumber is - " & x & Chr(13) _
& "activecell interior colornumber is - " & y & Chr(13), vbExclamation, "get cell's colors"
Exit Sub
errhandler:
MsgBox " sorry sucker, cell is empty"
End Sub


questions: what about cases when there is no font but the cell has an interior color .if cell has no interior color but has a font how can i replace the -4102 color number with the word "empty".is there an alternative to font number -4105.?
thanks

[Bob Phillips]

Sub findcolorfont()
Dim x As Long
Dim y As Long

If IsEmpty(ActiveCell) Then GoTo errhandler
x = ActiveCell.Font.ColorIndex
y = ActiveCell.Interior.ColorIndex
MsgBox "activecell font colornumber is - " & _
IIf(x = xlColorIndexAutomatic, "automatic", x) & vbNewLine & _
"activecell interior colornumber is - " & _
IIf(y = xlColorIndexNone, "none", y), vbExclamation, "get cell's colors"
Exit Sub
errhandler:
MsgBox " sorry sucker, cell is empty"
End Sub

[Jan Karel Pieterse]

A cell always has a Font object and always has an Interior object, regardless of content. So your error handler does nothing (and also wasn't initialised in the first place, there is no "On Error Goto" statement)

[Bob Phillips]

A cell always has a Font object and always has an Interior object, regardless of content. So your error handler does nothing (and also wasn't initialised in the first place, there is no "On Error Goto" statement)

He has an explicit GoTo to it on an empty cell condition.

[Jan Karel Pieterse]

Duh, of course.

I'm not used to using Goto's.
So if I see a label that is named "ErrHandler", I automatically assume it is meant to trap runtime errors (which are unlickely in the code sample).

[Bob Phillips]

Duh, of course.

I'm not used to using Goto's.
So if I see a label that is named "ErrHandler", I automatically assume it is meant to trap runtime errors (which are unlickely in the code sample).

I know what you mea, the message text gave it way to me :)

[Zack Barresse]

Why even use an error handler?? ..

Sub ShowMeTheColor()
Dim x As Long, y As Long
If Len(ActiveCell.Value) = 0 Then
MsgBox "The activecell is empty."
Else
x = ActiveCell.Font.ColorIndex
y = ActiveCell.Interior.ColorIndex
MsgBox "The font color index is: " & IIf(x < 0, 0, x) & vbNewLine & _
"The cell color index is: " & IIf(y < 0, 0, y), vbInformation, "Cell Color Info"
End If
End Sub

[Jan Karel Pieterse]

My point exactly.

[Bob Phillips]

Why not? Just because you wouldn't doesn't make it any more of an invalid approach (notwithstanding the GoTo). You could argue that it gives a more logically readable flow,

[Jan Karel Pieterse]

Well, this could become an endless discussion. Because how one handles things like these is a matter of taste, but before all a matter of programming style.

I use Style A, you use B and someone else might use C. Nothing wrong with either of those, as long as you produce code you can understand easily if you go back to it after some time.

My personal preference is to use error handlers only to handle errors (as much as possible, that is).

[Bob Phillips]

I don't disagree with anything you say, but as you quite rightly say, it is a matter of taste. So if the OP chooses to do it that way, that is perfectly fine IMO, and not wholly appropriate to question something just because one doesn't do it that way personally (I tried to say that as respectfully as I could :))