Problem with VBA in Excel version 2203 & 2204 64bit

Question

Problem with VBA in Excel version 2203 & 2204 64bit

Tirrazo 1

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

Michael Taylor 60,161 Reputation points

2022-04-29T18:22:54.577+00:00

You'll need to provide us more information before we can start to help you. "an issue" and "problem" don't tell us anything about what you're actually experiencing. Is it crashing? It is generating an error message? Is it just not running? Please provide us as much information as you can and perhaps someone can then help you.
Tirrazo 1 Reputation point

2022-04-30T10:05:41.863+00:00

I thought maybe someone had experienced that VBA codes have certain bugs in the beta versions and would reply with that.

But the problem I am experiencing is that the code does not work. It is not crashing, not giving any error message.
Its not running as it should and I know how it looks like when it's running since it works on a computer with a former Excel version.

9 answers

Your answer

Michael Taylor 60,161 Reputation points

2022-04-29T18:22:54.577+00:00

You'll need to provide us more information before we can start to help you. "an issue" and "problem" don't tell us anything about what you're actually experiencing. Is it crashing? It is generating an error message? Is it just not running? Please provide us as much information as you can and perhaps someone can then help you.
Tirrazo 1 Reputation point

2022-04-30T10:05:41.863+00:00

I thought maybe someone had experienced that VBA codes have certain bugs in the beta versions and would reply with that.

But the problem I am experiencing is that the code does not work. It is not crashing, not giving any error message.
Its not running as it should and I know how it looks like when it's running since it works on a computer with a former Excel version.

Answer 1

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.

Answer 2

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 Kristian

Michael Taylor 60,161 Reputation points

2022-05-02T14:22:52.31+00:00

Your script works for me but there are caveats.

1) For security reasons you need to ensure you save your Excel file with macros enabled. That is a different file extension. Save the file with the file type Excel Macro-Enabled Workbook otherwise macros are not going to run.
2) The script itself only highlights the rows D9+ because of the If statement at the top of it. So if you select any column from D on and row 9 on then it highlights the column and the row.

Answer 3

Tirrazo 1

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

Answer 4

Tirrazo 1

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

Michael Taylor 60,161 Reputation points

2022-05-03T18:06:35.86+00:00

I'm on 2203. Switch the file to enable macros and running your code works correctly for me for the selection change function. The formatting I cannot answer to as I don't know what you're using but the function handles the selection of the rows/columns so I don't know that the formatting would matter.

Answer 5

Tirrazo 1

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?

Michael Taylor 60,161 Reputation points

2022-05-05T14:48:08.077+00:00
The macro does that because of this line: Range("D9:DV512").FormatConditions.Delete. That line is removing all conditional formatting from the given range. This includes the formatting it adds and any existing formatting. You cannot remove that otherwise every selection adds more format conditions.

I think you're going to have to come up with a conditional format rule that solves this issue a different way. One approach that comes to mind is to use just conditional formatting (no macro) and if the currently selected column = the column being formatted OR the currently selected row = the row being formatted then highlight the row.

Create a conditional formatting rule for the worksheet (or subset of data you want to select). Set the formula to =OR(CELL("col")=COLUMN(), CELL("row")=ROW()). Set the formatting the way you want. The formula says to format the cell if the cell's column/row matches the current column/row that is selected. This is equivalent to what you were doing before.

Unfortunately Excel won't recalculate stuff unless there is a change so keep the macro you had but change it to force a recalculation when selection changes.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If Application.CutCopyMode = False Then Application.Calculate End If End Sub

Credit here for calculate - https://trumpexcel.com/highlight-active-row-column-excel/

Share via

Problem with VBA in Excel version 2203 & 2204 64bit

9 answers

Your answer