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

nadavrock

New Member
Joined
May 15, 2019
Messages
24
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?
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,439
Office Version
365, 2010
Platform
Windows, Mobile
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?
 

nadavrock

New Member
Joined
May 15, 2019
Messages
24
Re: how to make a sort by value on range change that keeps the cells formatting

yes by conditional formatting
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,439
Office Version
365, 2010
Platform
Windows, Mobile
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.
 

nadavrock

New Member
Joined
May 15, 2019
Messages
24
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,283
Messages
5,449,447
Members
405,567
Latest member
JeIIyfish

This Week's Hot Topics

Top