Excel Exits Macro on Font Color Change Line

[ransomedbyfire]

I have a macro that contains the following within a longer loop:

If InStr(Cells(Row, Column), "-") Then
    With Cells(Row, Column).Font
    .ColorIndex = 3
    End With
Else: With Cells(Row, Column).Font
    .ColorIndex = 0
    End With
End If

For some reason, the second time Excel hits the line in bold, the macro stops and starts over from the beginning; thus, it never loops past the second line or so. The only error handler I have sends the code to a msgbox line; so, I don't think it's that.

What could be causing this, and how can I fix it?

Thanks!

 

[Jonmo1]

1st, I recommend NOT using Row and Column as variable names.
It's not good to use VBA built in properties as variables.

That said, perhaps

With Cells(MyRow, MyColumn)
    If Instr(.Value, "-") >0 Then
        .Font.ColorIndex = 3
    Else
        .Font.Colorindex = 0
    End If
End With

 

[Rick Rothstein]

With Cells(MyRow, MyColumn)
    If Instr(.Value, "-") >0 Then
        .Font.ColorIndex = 3
    Else
        .Font.Colorindex = 0
    End If
End With

Please allow me to apologize in advance... the only excuse I can give you for what follows is "I'm bored!"

The code snippet you posted above can be replaced by anyone of the following alternatives (I'm sure there are others as well)...

With Cells(MyRow, MyColumn)
  .Font.ColorIndex = 3 * UBound(Split(.Value, "-", 2))
End With

With Cells(MyRow, MyColumn)
  .Font.ColorIndex = -3 * (InStr(.Value, "-") > 0)
End With

With Cells(MyRow, MyColumn)
  .Font.ColorIndex = Choose(1 - (InStr(.Value, "-") > 0), 0, 3)
End With

With Cells(MyRow, MyColumn)
  .Font.ColorIndex = 3 - 3 * (InStr(.Value, "-") = 0)
End With

With Cells(MyRow, MyColumn)
  .Font.ColorIndex = Mid("03", 1 - (InStr(.Value, "-") > 0), 1)
End With

With Cells(MyRow, MyColumn)
  .Font.ColorIndex = -3 * (.Value Like "*-*")
End With

With Cells(MyRow, MyColumn)
  .Font.ColorIndex = Choose(1 - (.Value Like "*-*"), 0, 3)
End With

With Cells(MyRow, MyColumn)
  .Font.ColorIndex = 3 - 3 * (Not .Value Like "*-*")
End With

With Cells(MyRow, MyColumn)
  .Font.ColorIndex = Mid("03", 1 - (.Value Like "*-*"), 1)
End With

Each of these was chosen because they are easily converted in one-liners by removing the With..End With block and distributing the Cells object to the dotted properties.

 

[ransomedbyfire]

Thanks, poster1. I tried what you suggested but am still having the problem. Poster2, thanks for the suggestions. I will have to come back to this when I have more time; but it looks like it could definitely make my code more efficient. I tried eliminating the with statement because I know it seems unnecessary; but for some reason, the computer I am writing this macro for doesn't like the one-liner. It has Excel 2003 and a few other weird issues.

 

[Rick Rothstein]

It sounds like your problem might be in another section of your code then.

Here is how to restructure my first alternative to eliminate the With..End With block...

Cells(MyRow, MyColumn).Font.ColorIndex = 3 * UBound(Split(Cells(MyRow, MyColumn).Value, "-", 2))

Note that all I did was put Cells(MyRow, MyColumn) in front of each property that starts with a 'dot'.

 

[Jonmo1]

If it's still not working, we'll need to see the whole code.

And of coarse the whole thing can be replaced with NO vba code at all..
Just using a simple conditional formatting formula

=SEARCH("-",A1)