• 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.

Highlight Cell

aparvez007

aparvez007

Member
I want to know how i can highlight the cell and respective cell heading in a perticular table. i m using below code to highlight the row and column.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

[SelRow] = Target.Row

[SelCol] = Target.Column


End Sub


but i want only 3 cell should highlight when i click on a particular cell.


please help...
 
Hi aparvez007,


Try this


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.Interior.ColorIndex = xlColorIndexNone

Union(Target.EntireRow, Target.EntireColumn).Interior.ColorIndex = 20

End Sub


Thanks,

Suresh Kumar S
 
Hi Parvez ,


I am not sure this is absolutely reliable , but you can try.


Place your cursor on cell A1 , and click on the entire worksheet select button , the one which is to the left of the column headers and above the row headers. If you want this to apply to only a selected range say D4:K19 , select the range A1:K19 ( since you want to include the first row and first column always ) and then do the following :


Select CF , and enter the following 3 rules :


=AND(SelRow=ROW(A1),SelCol=COLUMN(A1))


=AND(ROW()=1,SelCol=COLUMN(A1))


=AND(SelRow=ROW(A1),COLUMN()=1)


Narayan
 
Hi Parvez ,


It worked when I tried it !


Give the cell addresses of which cells should be highlighted ; suppose the cursor is on H13 ; other than H13 which are the other two cells which should be highlighted ?


Narayan
 
Hi Parvez ,


Does this mean that row 3 and column 3 will always be the other two cells ? For instance , if the cursor is on D27 , the other two cells will be D3 and C27 ?


Narayan
 
HI Aparvez007,


Can you please try the below for me..


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

[pre]
Code: 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub[/pre]
come back to workbook. Select entire Sheet,

* Apply Conditional Formatting > Formula as


[code]=OR(AND(CELL("row")=ROW(),CELL("col")+1>COLUMN()),AND(CELL("col")=COLUMN(),CELL("row")+1>ROW()))


Set the desired format..


Now in Apply to Section, write

=$C:$C,$3:$3[/code]


Please find the file for reference.

https://dl.dropbox.com/u/78831150/Excel/Highlight%20Cell%20%28aparvez007%29.xlsm


Regards,

Deb..
 
Dear Deb,


Thanks for your reply...


When i m clicking in any cell that cell is not highlighting only other two is highliting.... also its automatically not highlight, when i press F9 then only other two cell highlighted.


please help...
 
Excel highlights the active row and column on the pane.


See if the following link can be used:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=145


Please note that you'll have to do the code editing to suit the range size you need e.g.

Target.Resize(3,1).Interior.ColorIndex = 6
 
Hi Aparvez007


* Its automatically not highlighting as (I hope) you have not enable Macro.

* Please enable Macro to get effect without pressing F9 or change the code to below..

[pre]
Code: 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Calculate
End Sub[/pre]

* To highlight selected cell, you can add one more Conditional Formatting as

=AND(CELL("row")=ROW(),CELL("col")=COLUMN())


Please download the same file again, and confirm if its working as requested.


Regards,

Deb
 
Hi Shri..


OOPS you are there..

Now ball is out of my court.. :)


@ aparvez007,

If your sheet has some manual color or formatting, using shri.. / DRJ's technique will remove that.. be carefull.. :)


Regards,

Deb
 
If you say you need to press F9 [Debraj's code that is] to see it change then:

your sheet's calculation mode is set to "manual" which needs to be set to "automatic". Can you confirm this?
 
Dear Deb...


In your file its working fine but in my file need f9 to highlite...

from where i can change sheet's calculation mode "Automatic"...


Please help...
 
Upload your file with sensitive data removed. Maybe something is not matching with Deb's post but it needs to be checked.


@deb, we can play doubles, no problem ;)


EDIT: previous post removed as requested (SirJB7)
 
Hi Aparvez007,


I guess you have placed the VBA code to VBA>Insert>New Module Section.


You need to place the VBA code to VBA > Project > This Workbook Section.

Please recheck and confirm !! :)

[pre]
Code: 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub
[/pre]
Regards,

Deb
 
Yuppiii... working fine...


Deb caught me right...


Thanks.. Debraj/shrivallabha/NARAYAN/Suresh...


Sorry to trouble you...
 
You must log in or register to reply here.
Back
Top