Problem with VBA in Excel version 2203 & 2204 64bit

Tirrazo 1 Reputation point
2022-04-29T16:18:56.237+00:00

Hi,

I have an issue with a VBA code in Excel version 2203 & 2204.
On another PC I have version 2102 of Excel, the code works fine.

What do I do, I need it to work where I have the latest versions of Excel.
Can I go back from version 2203 to 2102 on that Excel, MS Office 365 account?

Shall I post the code here?

Regards

{count} votes

9 answers

Sort by: Most helpful
  1. Michael Taylor 51,071 Reputation points
    2022-04-30T15:10:17.54+00:00

    Is this VBA code inside a macro? If so then security comes to mind as files must be in a trusted location before they can be run. See here.

    The next thing to confirm is if the macro is being called at all. Open the macro and set a breakpoint on the first line. Then do whatever you need to do in your spreadsheet to trigger the code to run. If it doesn't run then either something has change to run it or security is getting in the way. If the breakpoint is hit then you can step through the code to try to determine what is failing.

    If you cannot figure it out then please provide the steps we need to replicate the issue including how to set up the VBA code to run (clicking a button, etc) and the code itself.

    0 comments No comments

  2. Tirrazo 1 Reputation point
    2022-05-01T13:17:01.577+00:00

    Hi,

    Thank you very much for your reply.
    I am not able to create these codes myself so I would appreciate if you could test it. The function is also combined with 2 conditional formatting rules.

    The solution I was after was to highlight the row and column from the cell I am standing at. This help me to see through large pages of similar numbers when you go through certain types of spreadsheets.

    This is the VBA inserted in 'Thisworkbook' module:

    Private Sub Workbook_Open()

    End Sub

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim TargetRange As String
    Dim TargetRow As String
    Dim TargetCol As String

    If Not Intersect(Target, Range("D9:DV512")) Is Nothing Then  
        Range("D9:DV512").FormatConditions.Delete  
        TargetRow = Cells(Target.Row, "D").Address & ":" & Cells(Target.Row, "DV").Address  
        TargetCol = Cells(9, Target.Column).Address & ":" & Cells(300, Target.Column).Address  
        TargetRange = TargetRow & "," & TargetCol  
        With Range(TargetRange)  
            .FormatConditions.Add _  
                Type:=xlExpression, _  
                Formula1:="TRUE"  
            With .FormatConditions(1)  
                .StopIfTrue = False  
                .Interior.ColorIndex = 15 'change to suit from dropdown  
            End With  
            .FormatConditions.Add _  
            Type:=xlExpression, _  
            Formula1:="TRUE"  
        End With  
    End If  
    

    End Sub

    Regards Kristian198024-false.png197987-true.png


  3. Tirrazo 1 Reputation point
    2022-05-02T14:32:15.277+00:00

    Hi,

    Thank you for testing it out.

    I am aware of the different type of workbook for macro enabled files. I have other macroes that work on the same computer, different workbooks.
    That is the weird thing.

    I have 3 different computers with 3 different versions of Excel. The two beta versions will not run this macro (the row and column will not highlight) but the PC without the beta version will run it.

    Regards

    0 comments No comments

  4. Tirrazo 1 Reputation point
    2022-05-03T15:51:46.243+00:00

    Can I ask what version of Excel you have tried it on?


  5. Tirrazo 1 Reputation point
    2022-05-05T08:20:45.377+00:00

    So you use the same version as me.

    What would you recommend me to do?

    I have another macro that is supposed to do the same without conditional formatting.
    That works, the only problem is that the VBA removes all the colors of the spreadsheet.

    Could you see if you can do anything with that code?