Highlighting column and row intersection cells?

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
521
Office Version
  1. 2016
Platform
  1. Windows
Hi everybody,

Any code or formula to highlight the intersected cells between a column and a row for any selected cell ?

Ex: if i select E25 the cells of column E1:E25 as well as the cells of Row A25:E25 to be highlighted in a certain color , and so on for any selected cell..

Thank you much for your help .
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
maybe this?

Code:
Sub test()
Range(Cells(1, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column)).Interior.Color = vbYellow
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, ActiveCell.Column)).Interior.Color = vbYellow
End Sub
 
Upvote 0
Hi,

Do you mean that the "color bands" are moving depending on the selection. Is it a way to see more clearly what cell is selected?

Then you can do a search for "color banding".

There are different techniques, which all have their advantages and limitations. I prefer to use conditional format. You can only apply it when you do not need conditional format for other purposes.

Select range
Formula for conditional format
=OR(ROW()=CELL("row"),COLUMN()=CELL("col"))

in the sheetmodule
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub

This will highlight more than you asked, but you can take this as a start.

kind regards,
Erik
 
Upvote 0
here's code that will change it automatically...you would put this in the sheet code (ie right click worksheet tab, view code)...

paste this in...this will change the highlighting based upon cell you select automatically...
just change
Code:
Set Rng = Range("A1:Z100")
to whatever range you want it to process...

Code:
Private Sub Worksheet_SelectionChange(ByVal target As Range)
 If target.Cells.Count > 1 Then Exit Sub
   Set Rng = Range("A1:Z100")
   If Not Intersect(target, Rng) Is Nothing Then
   Rng.Interior.Pattern = xlNone
   Range(Cells(1, target.Column), Cells(target.Row, target.Column)).Interior.Color = vbYellow
   Range(Cells(target.Row, 1), Cells(target.Row, target.Column)).Interior.Color = vbYellow
  End If
End Sub
 
Upvote 0
Dear Erik ,
The code with the conditional formatting is very close of what i need and as you said it is more of what i asked, it highlights more lower rows and more columns to the right. Thank you.


The code of TheNoocH worked great ! can i change color to whatever i need,what are the colors name ?

Thank you all guys !
 
Upvote 0
great glad it worked...
you can replace vbYellow with any of these color constants
vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, vbWhite

or you can change .interior.color to .interior.colorindex = # where # is any of the below numbers
 
Upvote 0
Oopss ! i changed the vbYellow to vbGray-40% and gave me a light blue color!
where is the problem ?
 
Upvote 0
you can only replace vbYellow with one of the color constants from my prior post...

in order to use Gray-40% you have to use color index...

change
Code:
   Range(Cells(1, target.Column), Cells(target.Row, target.Column)).Interior.Color = vbYellow
   Range(Cells(target.Row, 1), Cells(target.Row, target.Column)).Interior.Color = vbYellow

to

Code:
   Range(Cells(1, target.Column), Cells(target.Row, target.Column)).Interior.ColorIndex = 48
   Range(Cells(target.Row, 1), Cells(target.Row, target.Column)).Interior.ColorIndex = 48
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top