highlight row and column

[amkumar]

I need to highlight range in excel.
for eg. if i select "F6" in excel then column F and row 6 highlighted with color.

is this possible with conditional formatting ?? or there is any other option to do this.

 

[Jaafar Tribak]

I need to highlight range in excel.
for eg. if i select "F6" in excel then column F and row 6 highlighted with color.

is this possible with conditional formatting ?? or there is any other option to do this.

Yes, it can be done with conditional formatting and\or vba ... there are plenty of examples out there ... just do a search on this site or on the web.

 

[Paul Ked]

Possibly with CF, but this in the worksheet module will do it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    Dim rw As Long, cl As Long
    Sheet1.Cells.ClearFormats
    rw = Target.Row
    cl = Target.Column
    Rows(rw).Interior.Color = 65535
    Columns(cl).Interior.Color = 65535
    Application.EnableEvents = True
End Sub

 

[Peter_SSs]

Possibly with CF, but this in the worksheet module will do it:

It will, but I would be very wary about using it as it will also remove all existing formatting (cell colour either direct or Conditional Formatting, borders, text colour, bolding etc) from Sheet1. Further, without explanation this code may be applied to a sheet other than Sheet1 so that formatting destruction may even be on another worksheet. In that instance the sheet this is applied to will continually build up yellow colour in many rows/columns.

is this possible with conditional formatting ?

Yes it is and I prefer the Conditional formatting approach as it will not destroy any existing colour in the worksheet. Another advantage is that if the worksheet already has conditional formatting, this new CF will temporarily over-ride it so the selected row/column is clearly visible. (If other CF is added after the CF described here, it may hinder this & a slight adjustment could be needed. Post back if further information about this is required)

1. Select the whole worksheet (by clicking the box at the top left at the intersection of the column labels and row labels). We could select a lesser range but applying it to the whole worksheet doesn't seem to make files too big or slow in my experience.

2. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =CELL("row")=ROW() -> Format... -> On the Fill tab select the colour you want** for the entire row -> Ok -> Ok

3. With the whole sheet still selected go to Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =CELL("col")=COLUMN() -> Format... -> On the Fill tab select the same colour -> Ok -> Ok

4. Right click the sheet name tab and choose 'View Code'

5. Copy and Paste the code below into the main right hand pane that opens at step 4.

6. Close the Visual Basic window & test.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

** When choosing a colour, better to choose a colour that is not already used on the worksheet. On the Fill tab if you go to 'More Colors...' it would be easy to make a colour unique for your sheet.

A further advantage of this method it that even with the vba code involved, Excel's 'Undo' function is preserved.

 

[amkumar]

Thanks, the above example is working fine.
can i apply the same on multiple sheets in a workbook

 

[Peter_SSs]

Thanks, the above example is working fine.
can i apply the same on multiple sheets in a workbook

Best to indicate more clearly who you are referring to. In this case there are two examples "above" your post.
In any case, the answer is "yes"

 

[2Took]

It will, but I would be very wary about using it as it will also remove all existing formatting (cell colour either direct or Conditional Formatting, borders, text colour, bolding etc) from Sheet1. Further, without explanation this code may be applied to a sheet other than Sheet1 so that formatting destruction may even be on another worksheet. In that instance the sheet this is applied to will continually build up yellow colour in many rows/columns.


Yes it is and I prefer the Conditional formatting approach as it will not destroy any existing colour in the worksheet. Another advantage is that if the worksheet already has conditional formatting, this new CF will temporarily over-ride it so the selected row/column is clearly visible. (If other CF is added after the CF described here, it may hinder this & a slight adjustment could be needed. Post back if further information about this is required)

1. Select the whole worksheet (by clicking the box at the top left at the intersection of the column labels and row labels). We could select a lesser range but applying it to the whole worksheet doesn't seem to make files too big or slow in my experience.

2. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =CELL("row")=ROW() -> Format... -> On the Fill tab select the colour you want** for the entire row -> Ok -> Ok

3. With the whole sheet still selected go to Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =CELL("col")=COLUMN() -> Format... -> On the Fill tab select the same colour -> Ok -> Ok

4. Right click the sheet name tab and choose 'View Code'

5. Copy and Paste the code below into the main right hand pane that opens at step 4.

6. Close the Visual Basic window & test.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

** When choosing a colour, better to choose a colour that is not already used on the worksheet. On the Fill tab if you go to 'More Colors...' it would be easy to make a colour unique for your sheet.

A further advantage of this method it that even with the vba code involved, Excel's 'Undo' function is preserved.

Hi @Peter_SSs,

One caveat I experienced is that when you paste something into the range that is affected by this method, the coloring, or absence of color, transfers to and overrides this affected area, meaning this highlighting no longer works on the pasted area.

I my case I only needed a portion of my worksheet to have the row highlighted and I copy paste certain cells from above into this range.

Can you think of a fix to accommodate this scenario?

Cheers!

 

[Peter_SSs]

Can you think of a fix to accommodate this scenario?

I'm not experiencing what you seem to be describing. Perhaps my set-up differs to yours.
Can you set out in detail exactly what you have, where it is, what steps you are taking, what behaviour is happening and what behaviour you want to be happening instead?
Perhaps then I can set up a test workbook to see if I can suggest anything.