Results 1 to 5 of 5

Thread: Make a sort by value on range change that keeps the cells formatting
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Make a sort by value on range change that keeps the cells formatting

    i have

    Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("range2rating")) Is Nothing Then
    Call deadlines1
    End If
    End Sub

    Sub deadlines1()
    Range("range2").Sort _
    Key1:=Range("range2rating"), Order1:=xlDescending
    End Sub

    problem is that when it sorts based on value it doesnt keep the color fill of the cel. solution?

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,804
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: how to make a sort by value on range change that keeps the cells formatting

    How is the fill color applied? by conditional formatting?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to make a sort by value on range change that keeps the cells formatting

    yes by conditional formatting

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,804
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: how to make a sort by value on range change that keeps the cells formatting

    Can you please post a small sample of your data before sorting and your formula/condition for the conditional formatting.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to make a sort by value on range change that keeps the cells formatting

    data goes column heads: output, date placed, days since.
    out put are number. dates are days. days since is number fomrat of days passed. today()-date. conditional fomratting is a simple if greater than 7 yellow fill.
    i notice dthough i have the same problem with a simple fill without conditonal fomratting. the data moves by the sort macro but the fill doesnt

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •