How to get a macro to update the document?

[JenniferMurphy]

I have a macro that changes the colors in the selected text. Before we upgraded to Win 10 and Office 365, I'm pretty sure the page used to show the results as the marco operated. Now it doesn't until I exit the macro.

What command do I need to execute in the macro to get it to show the results while the macro is still working?

In Excel, I think the command is something like Activesheet.refresh, but I can't even find that.

Thanks

 

[PeteWright]

could you share your code?

 

[JenniferMurphy]

could you share your code?

It's very complicated. It's part of a larger macro. I can try to extract the relevenat portion.

But why does it matter? The code changes the font colors. I just want the new colors to be displayed as they are changed.

 

[PeteWright]

It's very complicated. It's part of a larger macro. I can try to extract the relevenat portion.

But why does it matter? The code changes the font colors. I just want the new colors to be displayed as they are changed.

I'm trying to understand the macro. Maybe there's something in it that has changed since MSO365.

Nevertheless, I don't know your code that changes the font, but maybe that is the culprit!
Could you please post the part of your code that changes the colors?

It should be something like:

X.Font.Color = Y

with X being some kind of an Object and Y being a number representing the color code.

 

[JenniferMurphy]

I'm trying to understand the macro. Maybe there's something in it that has changed since MSO365.

Nevertheless, I don't know your code that changes the font, but maybe that is the culprit!
Could you please post the part of your code that changes the colors?

It should be something like:

X.Font.Color = Y

with X being some kind of an Object and Y being a number representing the color code.

I wrote that code a long time ago. I am having a little trouble isolating the part that actually changes the font color.

While I'm working on that, can you post a code sample that will change the font colors in the selection to alternate between red (255,0,0 = 255) and green (0,255,0 = 65280)?

In your example above, I am guessing that Y would alternate between 255 and 65280. But what is X?

Thanks

 

[JenniferMurphy]

OK. Here's a simple version of the code. It sets the color of the characters in the selection to alternating red and green colors.

The page is updated after the color of first character is set, but not any of the rest until the macro exits.

Sub RandColorsTemp()

Dim RGBRed As Long:    RGBRed = RGB(255, 0, 0)
Dim RGBGreen As Long:  RGBGreen = RGB(0, 255, 0)
Dim RGBOrange As Long: RGBOrange = RGB(255, 128, 0)
Dim RGBBlack As Long:  RGBBlack = RGB(0, 0, 0)
Dim NextColor As Long
Dim obChar As Range

NextColor = RGBRed
For Each obChar In Selection.Characters
  If NextColor = RGBRed Then
    NextColor = RGBGreen
  Else
    NextColor = RGBRed
  End If
  obChar.Font.Color = NextColor
  If MsgBox("Continue", vbYesNo) <> vbYes Then Exit For
Next obChar

End Sub

 

[PeteWright]

OK. Here's a simple version of the code. It sets the color of the characters in the selection to alternating red and green colors.

The page is updated after the color of first character is set, but not any of the rest until the macro exits.

Sub RandColorsTemp()

Dim RGBRed As Long:    RGBRed = RGB(255, 0, 0)
Dim RGBGreen As Long:  RGBGreen = RGB(0, 255, 0)
Dim RGBOrange As Long: RGBOrange = RGB(255, 128, 0)
Dim RGBBlack As Long:  RGBBlack = RGB(0, 0, 0)
Dim NextColor As Long
Dim obChar As Range

NextColor = RGBRed
For Each obChar In Selection.Characters
  If NextColor = RGBRed Then
    NextColor = RGBGreen
  Else
    NextColor = RGBRed
  End If
  obChar.Font.Color = NextColor
  If MsgBox("Continue", vbYesNo) <> vbYes Then Exit For
Next obChar

End Sub

In my Excel 365 your code throws an error "Run-time error '438': Object doesn't support this property or method"
Debugging highlights following code part:

For Each obChar In Selection.Characters

So now I have two questions:

1. Which exact version of Excel do you use?
2. What should your code do?
   Could you make a screenshot of your text before and after the macro execution?

 

[JGordon11]

try

Sub RandColorsTemp()
    Dim RGBRed As Long:    RGBRed = RGB(255, 0, 0)
    Dim RGBGreen As Long:  RGBGreen = RGB(0, 255, 0)
    Dim Colors, i As Long
    Application.ScreenUpdating = True
    Colors = Array(RGBRed, RGBGreen)
    For i = 1 To Len(Selection)
        Selection.Characters(i, 1).Font.Color = Colors(i Mod 2)
        If MsgBox("Continue", vbYesNo) <> vbYes Then Exit For
    Next i
End Sub

 

[PeteWright]

try

Sub RandColorsTemp()
    Dim RGBRed As Long:    RGBRed = RGB(255, 0, 0)
    Dim RGBGreen As Long:  RGBGreen = RGB(0, 255, 0)
    Dim Colors, i As Long
    Application.ScreenUpdating = True
    Colors = Array(RGBRed, RGBGreen)
    For i = 1 To Len(Selection)
        Selection.Characters(i, 1).Font.Color = Colors(i Mod 2)
        If MsgBox("Continue", vbYesNo) <> vbYes Then Exit For
    Next i
End Sub

Hi @JGordon11 when running your code I get a "Run-time error '13': Type mismatch" at following line:

For i = 1 To Len(Selection)

What is wrong with my Excel and VBA? Is it only me or does anybody else get these errors?

 

[JGordon11]

Selection needs to be a single cell. If a range is selected the len function will be passed an array instead of a string yielding a type mismatch error.

If the original goal was for this to work on a range of cells then it would need to be modified.