Problem with VBA in Excel version 2203 & 2204 64bit

Question

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

Answer 1

Hi,

thank you very much for this.

This is actually the same conditional formatting rule + VBA I have been using the last 2 years.
I got it from The Excel Forum.

The only problem with it was that it started to make the workbook slow and moving around became almost impossible without the sheet lagging.

That's why I tried to find another way to solve it.

This VBA without formatting is working, but this one is removing all colour. That is a problem.

VBA:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim rRow As Range, rCol As Ranges
If Range("A1") = "" Then
Range("A1") = Target.Address
End If

With Range(Range("A1"))
    .EntireColumn.Interior.ColorIndex = 0
    .EntireRow.Interior.ColorIndex = 0
End With

Target.EntireRow.Interior.ColorIndex = 15
Target.EntireColumn.Interior.ColorIndex = 15
Range("A1").Value = Target.Address

End Sub

Answer 2

Hi again,

What could be the reason that the highlighting VBA is not highlighting in one computer with the latest Excel.
But with an earlier version it does.

Regards
Kristian

Answer 3

The first one I had was working for a long time. And I built my workbook bigger and that's probably why it got slower.

You are correct. I have the same code in a small spreadsheet for another thing I am working on and that is working smooth.

I am not able to configure these myself. Got help from the Excel forum, but they have no good solution either for this.
Lastly someone came up with the VBA that doesn't work on my computer and that's annoying.

Would it be possible to instead of highlighting the row, underline + right line from where you are? Like a cross that's following the marked cell.
Since the sheet is frozen both ways, that's why it's important that the lines go all the way from the beginning of the sheet and from the top, not just around the cell.
This is for me to better navigate when I move out from a part of the sheet that is frozen to work with numbers far on the right side.

Answer 4

Where do you see this: Application.Calculate ?

I have got another code that will not start. Can you see anything wrong?
It is supposed to draw underline and right line.

When I paste it in my book it won't draw any lines.

1) To ThisWorkbook code module

Copy to clipboard
Private Sub Workbook_Open()
If ActiveSheet Is Sheets("ark1") Then Run "sheet1.worksheet_activate"
End Sub

2) To Ark1 sheet code module

Copy to clipboard
Option Explicit

Private myCell As Range

Private Sub Worksheet_Activate()
Set myCell = ActiveCell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not myCell Is Nothing Then
myCell.EntireRow.FormatConditions.Delete
myCell.EntireColumn.FormatConditions.Delete
End If
With ActiveCell
With .EntireRow.Resize(, .Column)
.FormatConditions.Delete
.FormatConditions.Add 2, , "=row()=" & .Row
With .FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = 2
End With
End With
With .EntireColumn.Resize(.Row)
.FormatConditions.Delete
.FormatConditions.Add 2, , "=column()=" & ActiveCell.Column
With .FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = 2
End With
End With
Set myCell = .Cells
End With
End Sub