• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code to hightlight the current position - modification

vijay.vizzu

vijay.vizzu

Member
Dear All,


I have an below code to highlight the current position of the cell for better visualization. But when i run this code, it will erase all the current formating. So i want to put two buttons (start and stop)to run the code, If i click start, it need to be highlighted the current cell position, if i click stop it has to be stopped and return back to my current formatting. Kindly modify the code as per my requirement.


Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim RngRow As Range

Dim RngCol As Range

Dim RngFinal As Range

Dim Row As Long

Dim Col As Long


Cells.Interior.ColorIndex = xlNone


Row = Target.Row

Col = Target.Column


Set RngRow = Range("A" & Row, Target)

Set RngCol = Range(Cells(1, Col), Target)

Set RngFinal = Union(RngRow, RngCol)


RngFinal.Interior.ColorIndex = 36


End Sub


Thanks in advance


Vijay
 
All of this would go on the worksheet module. Then have your two buttons assigned to the TurnOn and TurnOff macros.

[pre]
Code: 
Option Explicit

Dim xOff As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim RngRow As Range
Dim RngCol As Range
Dim RngFinal As Range
Dim Row As Long
Dim Col As Long

If xOff Then Exit Sub
Cells.Interior.ColorIndex = xlNone

Row = Target.Row
Col = Target.Column

Set RngRow = Range("A" & Row, Target)
Set RngCol = Range(Cells(1, Col), Target)
Set RngFinal = Union(RngRow, RngCol)

RngFinal.Interior.ColorIndex = 36

End Sub

Sub TurnOn()
'Enable highlights
xOff = False
End Sub

Sub TurnOff()
'disable highlights
xOff = True
End Sub
[/pre]
 
@Luke.. Thanks Luke, for show me path...:)


Hi Vijay,


Why dont you save the formats before start (at 1st button click) and again rollback the format in old style after complete(at 2nd button click)


can you please change a little bit in Luke's Coding..

______


Dim xOff As Boolean


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim RngRow As Range

Dim RngCol As Range

Dim RngFinal As Range

Dim Row As Long

Dim Col As Long


If xOff Then Exit Sub

Cells.Interior.ColorIndex = xlNone


Row = Target.Row

Col = Target.Column


Set RngRow = Range("A" & Row, Target)

Set RngCol = Range(Cells(1, Col), Target)

Set RngFinal = Union(RngRow, RngCol)


RngFinal.Interior.ColorIndex = 36


End Sub


Sub TurnOn()

'Enable highlights

xOff = False

Sheets("sheet1").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

ActiveSheet.Name = "SavedFormat"

Sheets("SavedFormat").Visible = xlSheetHidden

Sheets("sheet1").Select

End Sub


Sub TurnOff()

'disable highlights

xOff = True

Sheets("SavedFormat").Visible = xlSheetVisible

Sheets("SavedFormat").Cells.Copy

Sheets("sheet1").Range("a1").PasteSpecial xlPasteFormats

Application.DisplayAlerts = False

Sheets("SavedFormat").Delete

Application.DisplayAlerts = True

Sheets("sheet1").Range("a1").Select

End Sub
 
Why not use conditional formatting to highlight the selected row / column. It preserves the formatting too. Follow these steps.


1. set up 2 cells (in a separate sheet) to keep track of selected row & column.

2. name these cells as selRow and selCol

3. In the sheet where you need highlighting, use worksheet_selectionchange and write below code


selRow = target.row

selCol = target.column


4. Now, select the entire sheet (or just the area that needs highlighting) and apply conditional formatting with rules like below:

5. row(cell) = selRow

6. set up highlight color

7. column(cell) = selCol

8. set up highlight color

9. done.
 
Dear Chandoo and others,


Sorry for the delay. Chandoo sir i have tried your way, but i face one problem while applying conditional formating. In my workbook i have created a new sheet and, Cell A1 i named it selRow & cell B1 i named it selCol. Now the sheet which i want to highlight the current cell position, i put code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


selRow = Target.Row

selCol = Target.Column


End Sub

and that sheet i selected entire sheet & in conditional formatting -> use formula to highlight the cell-> =A1=selRow -> format color, again use formula -> =B1=selCol -> format color and done.


But i can't work.... is there any mistake in procedure


Thanks

Vijay
 
Hi Vijay,


Please try the below.

in VBA editor, select This Workbook Module, and then Workbook Module, SheetSelectionChange events write the below code.

'

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Application.ScreenUpdating = True

End Sub

'


come back to workbook. Select entire Sheet,

* Apply Conditional Formatting > Formula as


'=OR(AND(CELL("row")=ROW(),CELL("col")+1>COLUMN()),AND(CELL("col")=COLUMN(),CELL("row")+1>ROW()))


choose desired format for the cell, It will preserve your previous formatting, and done the same what VBA code is doing.

* where VBA code is change color for entire selection, this color coding is only for the top-left selection, or cells(0,0) :)


Please also confirm if the above modified VBA is working or not. :)


First thanks to @Luke & then to @chandoo, for the same reason, show me path..:)
 
@Blogger: Please help how to write post in proper format, <b> only bold is enough <b> and in programming format. I tried many times referring below information,


Allowed markup: a blockquote code em strong ul ol li.

You can also put code in between backtick ( ` ) characters.


but always fails :( and realize after post.. :(
 
@Debraj


Have a read of http://chandoo.org/wp/2011/11/04/fancy-posts-using-html-display-codes/
 
@ HUI..

Code: 
you are great.. not only Excel/VBA/Formula but also for quickly search similar thread..:)

I got it..
 
Debra


I can remember most posts here at Chandoo.org,

Especially the ones I wrote like that one.
 
You must log in or register to reply here.
Back
Top