Border Around entire row of selected cell

[The Animal]

Hi
I need to highlight the entire row in a fill color (Say Yellow) of the selected cell but I still need to see any of the original fill colors including the cell I select. I have used various options but they all cover the fill for all cells in that row with the new highlight color.
A heavy red border would also be great as long as you can see the original fill color behind.
Thanks Stephen

 

[My Aswer Is This]

How do you think that can be possible?
If you fill a cell with a color how do you think it would be possible to see the fill color that was previously in the cell.
Maybe I do not understand what you want.

 

[NoSparks]

Hello The Animal Stephen,

I believe this macro will do what you're wanting

Sub HighLightRow()
   Dim rng As Range
   Dim cel As Range
   Dim origAddress As String
   Dim origColor As Integer
   
Application.ScreenUpdating = False

'preserve active cell and color
origAddress = Replace(ActiveCell.Address, "$", "")
origColor = ActiveCell.Interior.ColorIndex

'highlight row yellow
Set rng = ActiveCell.EntireRow.Cells
For Each cel In rng
    If cel.Interior.ColorIndex = -4142 Then
        cel.Interior.ColorIndex = 6
    End If
Next cel

'make sure active cell remains original color
Range(origAddress).Interior.ColorIndex = origColor

'red border round entire row
With rng
    With .Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 3
        .Weight = xlMedium
    End With
    With .Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 3
        .Weight = xlMedium
    End With
    With .Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 3
        .Weight = xlMedium
    End With
        With .Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 3
        .Weight = xlMedium
    End With
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
End With
    
Application.ScreenUpdating = True

End Sub

 

[Peter_SSs]

I need to highlight the entire row in a fill color (Say Yellow) of the selected cell but I still need to see any of the original fill colors including the cell I select.

Try this Worksheet_SelectionChange code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  On Error Resume Next
  With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.ColorIndex = 27
    .ReplaceFormat.Interior.ColorIndex = xlNone
    Cells.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
    .FindFormat.Interior.ColorIndex = xlNone
    .ReplaceFormat.Interior.ColorIndex = 27
    Target.EntireRow.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
  End With
End Sub

 

[NoSparks]

Wow Peter, never even thought of that as being what the OP wanted
but I sure like it.

 

[Peter_SSs]

Wow Peter, never even thought of that as being what the OP wanted

Well, we're yet to hear from the OP if that is it but it was my interpretation of the post.

I did mean to mention two other point about my suggestion:
1. The colour used in the code (yellow with ColorIndex = 27) needs to be different to any existing coloured cells in the sheet otherwise they will have their colour removed by the code.
2. It can become a bit 'flakey' and not always perform exactly as it should. Not sure of the exact circumstances that occasionally causes the problem but it may be a little better anyway if we restrict the operation of the code to the used range on the sheet by altering these two lines in the code.

    ActiveSheet.UsedRange.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
    
    
    Intersect(Target.EntireRow, ActiveSheet.UsedRange).Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True

 

[Rick Rothstein]

1. The colour used in the code (yellow with ColorIndex = 27) needs to be different to any existing coloured cells in the sheet otherwise they will have their colour removed by the code.

Why not use the Color property (which was how I originally proposed it) and just subtract 1 from the VB constant vbYellow instead of using the ColorIndex? Subtracting one that way will make the color different enough from what people normally use and will avoid the problem.

2. It can become a bit 'flakey' and not always perform exactly as it should. Not sure of the exact circumstances that occasionally causes the problem...

I never noticed any problem with it when I tested it originally. I understand you can't tell me what triggers the problem, but could you describe "a bit 'flakey'" for me so I know what to look for?

 

[Peter_SSs]

Why not use the Color property (which was how I originally proposed it) and just subtract 1 from the VB constant vbYellow instead of using the ColorIndex? Subtracting one that way will make the color different enough from what people normally use and will avoid the problem.

Rick, I'm not sure what you are referring to when you say "how I originally proposed it", but the answer is 'no particular reason'. I agree that what you suggest would almost certainly avoid the potential problem but there is no absolute guarantee of that & ultimately the user must decide on what colour suits their circumstances best.

I never noticed any problem with it when I tested it originally. I understand you can't tell me what triggers the problem, but could you describe "a bit 'flakey'" for me so I know what to look for?

Some of the things that happened while I was testing (Excel 2010):
- Only, say, columns D:H in the selected row became yellow
- No cells in the selected row turned yellow
- The yellow did not get removed from the previously highlighted row.
- The yellow only got removed from part(s) of the previously highlighted row, leaving, say, columns E:Y yellow.

 

[Rick Rothstein]

Rick, I'm not sure what you are referring to when you say "how I originally proposed it"...

I had figured you used code (either directly or constructed from a memory based on a past reading) that I have been posting for this functionality for over a year now as the basis for the code you posted in Message #4...

http://www.mrexcel.com/forum/excel-questions/835688-highlighting-active-row-problem.html#post4073802

I do see, however, that the idea of subtracting one from the color value did not occur to me until a later date. I swear Google used to have a better search engine as it can no longer find links to postings whose keywords I know. Anyway, a quick look turned up my posting to a different website (see first comment towards the bottom of the page) as the first mention of subtracting 1 from the vbYellow value...

Highlight Selected Cells in Excel and Preserve Cell Formatting

I am pretty sure that I posted that concept on MrExcel first, but the only reference I could find was for this later dated posting...

http://www.mrexcel.com/forum/excel-...ving-color-fills-other-cells.html#post4551321

 

[Peter_SSs]

I had figured you used code (either directly or constructed from a memory based on a past reading) that I have been posting for this functionality for over a year now ..

No, the basis of my code was much older than that.