question

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.

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

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

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

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?

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

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

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.

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