Highlight entire row and move to bottom

[Tarvalon]

I need a function or conditional format that will highlight an entire row if cell J:J = "P" and move to the bottom of the data table. I am using Excel 2013.

Thanks in advance.

 

[RickXL]

Hi.

If I needed to do this I think I would create a conditional format to highlight the row. Then create a macro that was triggered by a worksheet change event to move the rows with a "P" in.

I suspect you could select the rows to move then cut and paste them to the end. However, a possibly simpler solution would just be to sort the rows into order. If you can use column J directly then that will be easiest. Otherwise you will need either a custom sort list or a helper column. The sorting could be triggered by a worksheet change event to make it automatic.

Regards,

 

[Tarvalon]

Hi, thanks for the reply. I never thought of sorting. I always try to think of the complicated stuff that the easy stuff just doesn't occur to me.

I don't know how to create conditional formatting to highlight the entire row. I just know how to highlight the cell.

Thanks

 

[RickXL]

Hi.

I have to admit that me and Conditional Formatting don't always get on
Excel usually finds an excuse for not doing what I want it to.

However, the trick in this case is to:

1. Select all the data
2. Select Home-->Styles-->Conditional Formatting
3. Select "Use a formula to determine which cells to format"
4. Enter the formula as: =$J2="P"
(Note the "$" sign before the "J".)
5. Select the Format button
6. Set the format and click OK
7. Click OK again


If you prefer the macro approach, here is a recording I made using the range A2:J15:

Sub SetConditionalFormat()
    Range("A2:J15").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J2=""P"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
End Sub

 

[Tarvalon]

I did your conditional formatting suggestions and it would turn the row above the color I needed. Any ideas?

I forget where to put macros in.

Thanks for your help.

 

[Tarvalon]

Nevermind. I got it to work. Thanks for your help.