have the fill format move with the data when sorting

still learning · Nov 21, 2017

Hi,

I have a spreadsheet that has data from A to J
A has a date in it
Every other line from A to J is formatted with a fill of RGB: Red 195..Green 252..
Blue 253

When I’m done entering the date, I use the following macro to sort it based on the date in A. (I used the macro recorder to come up with the macro)

HTML:

Sub atoz ()

      With ActiveSheet
          .Range("A3", .Range("A" & Rows.Count).End(xlUp)).Resize(, 8).sort _
              Key1:=.Range("A11"), Order1:=xlAscending, Header:=xlNo, _
              OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
              DataOption1:=xlSortNormal
      End With
  End Sub

If the data has to move because of the date, the fill formatting does not move. I then have to redo the formatting by changing the lines that is supposed to have no fill to “No Color” and changing the others to the RGB color.

Is there a way to have the fill format also move when the data moves

Mike

My Aswer Is This · Nov 21, 2017

What is the reasoning for every other row to be colored.
If we knew the reasoning we could then reset the row colors.

Or can we just tell the script to color every other row.

still learning · Nov 22, 2017

Hi
The reason for every other line a fill color is only for “looks”. It makes it easier for me to see the lines all the way across.
So one line has no fill color and the next line has that bluesh color. Then the next
line has no fill color and the next line has the same bluesh color
Actually, it’s a check register I made.
I enter the date and then the info across.
When I’m done, I sort the range.
The lines move but the fill doesn’t.
I then highlight the first wrong line down to where it is correct (sometimes, I have to go past the last entry) and use format to make everything correct.
I was wondering if when I sort and the line moves, the fill format would move with it.

I probably could add to the macro to un-fill the whole range then fill every other line down past the last row, but I was wondering if something would be easier. and it would be a learning thing

I can attach a sample if it would make it easier to understand. I’m not the best at describing things

mike

Fluff · Nov 22, 2017

How about using conditional formatting.
Select the used area > condition formatting > New Rule > Use a formula > =MOD(ROW(),2) > select the format you want > ok

My Aswer Is This · Nov 22, 2017

My Aswer Is This · Nov 22, 2017

Try this:

Code:

Sub atoz()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
      With ActiveSheet
          .Range("A3", .Range("A" & Rows.Count).End(xlUp)).Resize(, 8).Sort _
              Key1:=.Range("A11"), Order1:=xlAscending, Header:=xlNo, _
              OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
              DataOption1:=xlSortNormal
      End With
  Rows("3" & ":" & Lastrow).Interior.ColorIndex = xlNone
  
  
    For i = 4 To Cells(Rows.Count, "A").End(xlUp).Row Step 2
        Rows(i).Interior.Color = RGB(195, 252, 253)
    Next
    Application.ScreenUpdating = True
  
End Sub

still learning · Nov 22, 2017

Hi Fluff,
I tried that but the fill didn't go with the data

My Aswer Is This
I tried your macro
works great except I'd like to make a couple of modifications, which I don't know how to do.

1) how do I stop the fill after J column
2) how do I have the fill go about 12 lines past the last data entry.

I was playing around with this put I have to put the cursor in A on the first line to fill and run the macro for each fill
It fills the line up to J and drops down 2

HTML:

Sub addnewfill()
        ' put cursor on line to color
    ActiveCell.Range("A1:J1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16645315
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(2, 0).Range("A1").Select  
End Sub

In case I don't get back today:: Happy Thanksgiving to you and your family.
Fluff
........and
My Aswer Is This

Mike

My Aswer Is This · Nov 22, 2017

Try this:

Modify added rows you asked for:
See code marked in red to change. Now set to 12

Code:

Sub atoz()
'Modified 11-22-17 1:30 PM EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As Long
ans = [COLOR=#ff0000]12[/COLOR] ' Modify this to add more Now set to 12
      With ActiveSheet
          .Range("A3", .Range("A" & Rows.Count).End(xlUp)).Resize(, 8).Sort _
              Key1:=.Range("A11"), Order1:=xlAscending, Header:=xlNo, _
              OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
              DataOption1:=xlSortNormal
      End With
  Rows("3" & ":" & Lastrow + ans).Interior.ColorIndex = xlNone
    
    For i = 4 To Cells(Rows.Count, "A").End(xlUp).Row + ans Step 2
        Range(Cells(i, "A"), Cells(i, "J")).Interior.Color = RGB(195, 252, 253)
    Next
    Application.ScreenUpdating = True
  End Sub

still learning · Nov 25, 2017

Thank you My Aswer Is This
it works perfectly

Mike

My Aswer Is This · Nov 25, 2017

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

still learning said:
Thank you My Aswer Is This
it works perfectly

Mike

have the fill format move with the data when sorting

still learning

Well-known Member

Excel Facts

My Aswer Is This

Well-known Member

still learning

Well-known Member

Fluff

MrExcel MVP, Moderator

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

still learning

Well-known Member

My Aswer Is This

Well-known Member

still learning

Well-known Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

have the fill format move with the data when sorting

Well-known Member

Excel Facts

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock