VBA Highlight Column & Row with Border Based on Selection

[pure vito]

Hi All

I'm here looking for suggestions today and hopefully help, below is an image of my document the user fills H2:M... the issue I'm having is that it's very difficult to tell if your inputting into the correct cell,

I looked into using a VBA that highlights the selected column and row with colour but it basically removes all current colour this isn't really an option, In the image I have used a thick border to create a line do you think its at all possible to have this act in the same way as the VBA highlighter but with borders?

 

[James006]

Hi,

Your sheet is so much much overloaded ...!!! with too many visual information ... you usually end up with no information at all ....

What is your real objective ???

 

[pure vito]

Hey James I agree the document is up 42,000kb but it visually has to be this way for me it's the only colour in my life right now

Jokes aside Id just like to make it easier to see the row selected, column A is where we are looking when inputting data into H:M,

Another option would be to move column A to G but that's really not something I want to do,

Is it possible to have a VBA option that marks the row and column as pictured with a thick border?

 

[HaHoBe]

Hi pure vito,

maybe change RowHeight and ColumnWidth for the cell in question by using

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Const clngZoom As Long = 30

With ActiveSheet.UsedRange
  .EntireRow.AutoFit
  .EntireColumn.AutoFit
End With

If Target.Count > 1 Then Exit Sub
If Target.Column >= 8 And Target.Column <= 13 Then
  Target.EntireRow.RowHeight = clngZoom
  Target.EntireColumn.ColumnWidth = clngZoom
End If
End Sub

behind the worksheet in question.

Holger

 

[pure vito]

Hey HaHoBe, That looks like a great option thank you , I have tried it but it seems to narrow columns to the right of the document outside the VBA range

The border highlight option would be awesome if it were possible

 

[Peter_SSs]

but it basically removes all current colour

Is the current colour done by Conditional Formatting or are the cells coloured normally?"

 

[pure vito]

Is the current colour done by Conditional Formatting or are the cells coloured normally?"

Hey Peter, The cells have been coloured normally should I create conditional formatting for them to revert back?

 

[HaHoBe]

Hi pure vito,

altered code to work only on a limited range:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rngWork As Range

Const clngZoom As Long = 30

Set rngWork = Range("H2:M133")

With rngWork
  .RowHeight = 12   'change to suit
  .ColumnWidth = 5  'change to suit
End With

If Target.Count = 1 Then
  If Not Intersect(Target, rngWork) Is Nothing Then
    Target.EntireRow.RowHeight = clngZoom
    Target.EntireColumn.ColumnWidth = clngZoom
  End If
End If

Set rngWork = Nothing
End Sub

Ciao,
Holger

 

[Peter_SSs]

The cells have been coloured normally should I create conditional formatting for them to revert back?

Try following the steps detailed here: highlight row and column

 

[pure vito]

Guy's thank you so much this has really helped,

@Peter_SSs thank you for you suggestion your time is always appreciated likewise @James006

I think we have found a solution @HaHoBe this VBA works amazingly well for this, thank you so much for sharing