Simple routine with If now does not work – help please!

25 pts.
Tags:
Microsoft Excel
VBA
In Excel sheet I had macro for hiding and showing all rows that had a red cell in Column A.  It was working well but now hangs. Any clues as to why this is happening and how to get round it?
This below is the Show routine which unhides any row with a red cell in Column A as well as showing Column A.
Thank you for looking, and more so if you can help.
Sub Unhide_Rows()
'
' Unhide_Rows Macro
' Unhides hidden rows & Column A
Application.ScreenUpdating = False
Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("A2:A600")
For Each MyCell In Rng
If MyCell.Interior.ColorIndex = 3 Then
MyCell.EntireRow.Hidden = False
End If
Next MyCell
Range("A:A").EntireColumn.Hidden = False
Application.ScreenUpdating = True
End Sub


Software/Hardware used:
EXCEL 365, Windows 10
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    You said it was working well but now it hangs. I assume you have not changed the macro, it may be due to an Office upgrade. Check the last update and see if that coincides with your problem..
    127,470 pointsBadges:
    report
  • Madcap55
    Hi there - I was suspecting that could be a possibility - any pointers for working out if this is the cause? I have Office 365 which downloads and updates automatically. Going to Account/Office Updates it is not easy to see when and what was updated.

    I do not think I have changed the macro in any way. 
    Tony
    25 pointsBadges:
    report
  • ToddN2000
    You may have already done this but have you checked to see if the security settings to run the macro have been changed?

    Here is a link to Microsoft's macro-security-settings
    127,470 pointsBadges:
    report
  • Madcap55
    I have a number of macros based in the spreadsheet. Generally they seem to work.

    I may have got round it by copying the VBA for the macro from a version saved 3 months ago. I cannot see any difference between the 2, having checked every letter, but for the moment it seems to have resolved the issue.

    Once again for thank you for your responses.
    25 pointsBadges:
    report
  • ToddN2000
    Glad you got a work around. Strange solution if all the code looks the same. If I come across anything else that may have caused it I'll post it back here for future reference.
    127,470 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: