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

 

[Rick Rothstein]

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

Ah, and here I thought I "invented" that technique... instead, it turns out I was just a johnny-come-lately rediscoverer.

 

[Peter_SSs]

Ah, and here I thought I "invented" that technique... instead, it turns out I was just a johnny-come-lately rediscoverer.

Hehe, that's usually the way it happens.
And even if we can't find a precedent for something we think we've worked for the first time, it probably just means we haven't searched well enough.

Do you agree that the following can be omitted from Harry's and/or your code?
I did put it temporarily back in mine to see if that was causing the occasional problems I was having but that still occurred so I took it out again.

With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.ColorIndex = 27
    .ReplaceFormat.Interior.ColorIndex = xlNone
    Sh.Cells.Replace What:="", Replacement:="", SearchFormat:=True, _
    ReplaceFormat:=True
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.ColorIndex = xlNone
    .ReplaceFormat.Interior.ColorIndex = 27
    Intersect(Target.EntireRow, Sh.UsedRange).Replace What:="", _
    Replacement:="", SearchFormat:=True, ReplaceFormat:=True
End With

 

[Rick Rothstein]

Do you agree that the following can be omitted from Harry's and/or your code?
I did put it temporarily back in mine to see if that was causing the occasional problems I was having but that still occurred so I took it out again.

With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.ColorIndex = 27
    .ReplaceFormat.Interior.ColorIndex = xlNone
    Sh.Cells.Replace What:="", Replacement:="", SearchFormat:=True, _
    ReplaceFormat:=True
    <del>.FindFormat.Clear</del>
    <del>.ReplaceFormat.Clear</del>
    .FindFormat.Interior.ColorIndex = xlNone
    .ReplaceFormat.Interior.ColorIndex = 27
    Intersect(Target.EntireRow, Sh.UsedRange).Replace What:="", _
    Replacement:="", SearchFormat:=True, ReplaceFormat:=True
    .ReplaceFormat.Clear
End With

In thinking about it, yes, you can remove them. I actually remember putting those in my original code... the thought was that the formats are cumulative, but that cumulativeness only applies to differing formats (green fill, bold red font and such), but each individual format can only contain one setting (you cannot have both green and blue fills at the same time... it is either blue or green singly) and here we are replacing one fill with another, so there is no need to clear them where you show them struck through. However, I would say you should include the final one I show above in red because the ReplaceFormat interior ColorIndex of 27 will remain set after your code has finished and clearing it will prevent the user from accidentally "tripping over it" in future calls to the Replace dialog box. I know in my original code I also cleared the FindFormat but since its ColorIndex is set to xlNone, that effectively cleared it already, so there is no need to repeat that action.

 

[luisftv]

Peter_SSs

I have been looking for quite some time for a VBA solution to highlight the row/column of the active cell that would not remove CF, font formatting, or cell borders and it has proven almost impossible, up until the other day that I stumbled upon this thread.

Your solution is the best I've found. The only thing that it will not work with is undo/redo, and that's fine.

However, I have a question: How can your code be modified to show temporary cell borders instead of a temporary "fill" color and still not remove any existing or future cell fill, font formatting, cell borders, or any conditional formatting, etc?

Of course, if the solution will use "temporary" cell borders, for the row the setting will be to use "top/bottom" borders; and for the column it will use "left/right" borders.

Just in case, this is your code I'm referring to which does not give any errors. It simply works flawlessly (I've tried in in Excel 2010, 2013, 2016, 2021).

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

Thanks for your help.

 

[Peter_SSs]

@luisftv
To clarify, is this what you are after if cell H3 is selected?

What do you want to happen if a multi-cell range is selected (eg H3:I5)?

What about if a multiple areas are selected at once (eg B2:C5 and K10:P11)?

 

[luisftv]

Peter_SSs,

Your original code does exactly what I want. I simply want that instead of a fill color to use borders as in the screenshot you just gave.

I actually want to use a fill color for the row and left/right borders for the column. Or maybe later I will flip these two.

See this screen shot below and see the attached file which is using CF and VBA - but I need a solution that will only use VBA, such as yours.

So, the row has a fill color, and the column has left/right border instead of a fill color. BUT it needs to allow for cell formatting (font color, bold, italics, underline, fill color, highlight, CF, borders, etc) just as your present solution does.

If I select a group of cells, a range, etc., it should not do anything. It should behave the normal way Excel would. I only need to "highlight the row and column of the active cell", the ONE active cell.

Thanks Peter_SSs.

 

[luisftv]

Oops... I forgot to attach the file in the previous post, and it won't let me upload any.

But the screenshot says it all, I think.

Later, in another workbook, I will need to use borders instead of fill so I would like to be able to interchange between fill color and borders.

Thank you so much.

Ps. I am using Excel 2010 (not that it matters when using VBA though).

 

[Peter_SSs]

I am using Excel 2010 (not that it matters when using VBA though).

In some circumstances it can matter. In any case I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sorry, I am not having any success in adapting this to add/remove the borders that you want.

 

[luisftv]

Thanks for trying.

I have found different approaches all over the net and I've tried combining them to no avail (to get the result I need). I found code for using only lines and I've combined them with your solution, but it is still removing the borders as you move to another cell. I am posting the code here to see if anyone comes up with a solution:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    'This way of using borders to highlight the column or row of the active cell will not allow to use manual cell borders
    Cells.Borders.LineStyle = xlNone
    With ActiveCell
        'With .EntireRow
        '    With .Borders(xlEdgeTop)
        '        .Color = RGB(0, 176, 80) 'green (change as desired)
        '        .LineStyle = xlContinuous
        '        .Weight = xlMedium 'or xlThin, xlThick, and xlHairline (change as desired)
        '    End With
        '    With .Borders(xlEdgeBottom)
        '        .Color = RGB(0, 176, 80)
        '        .LineStyle = xlContinuous
        '        .Weight = xlMedium
        '    End With
        'End With
        With .EntireColumn
            With .Borders(xlEdgeLeft)
                .Color = RGB(0, 176, 80)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
            With .Borders(xlEdgeRight)
                .Color = RGB(0, 176, 80)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End With
    End With
 
  On Error Resume Next
  With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.ColorIndex = 15
    .ReplaceFormat.Interior.ColorIndex = xlNone
    Cells.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
    .FindFormat.Interior.ColorIndex = xlNone
    .ReplaceFormat.Interior.ColorIndex = 15
    Target.EntireRow.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
    'Target.EntireColumn.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
 
    'This, when enabled will not let Format Painter work properly
    'FontSize = ActiveCell.Font.Size
    'LargeSize = FontSize * 1.1
    'Cells.Font.Size = FontSize
    'ActiveCell.Font.Size = LargeSize

  End With
 
End Sub

I'll keep trying.