VBA Data Sort (Keep Cell Formating while Only Moving Text)

[scott_86_]

Hi, I currently have a code that automatically sorts data on multiple worksheets when the workbook is open and it works fine.

Most cells in the E9:J28 Range have no fill colour; however, there are some cells (in rows) that are intentionally blacked out.

Can anyone help me out to tweak this VBA code so that:
- Data still gets sorted the same.
- ONLY the text moves.
- All cells in the E9:J28 range keep their current formatting (the only format difference in the range is fill colour as mentioned).

Thanks in advance!

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name <> "Data" Then

'Top Left
        ws.Sort.SortFields.Clear
        ws.Sort.SortFields.Add Key:=Range("J9:J28"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ws.Sort.SortFields.Add Key:=Range("E9:E28"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ws.Sort
            .SetRange Range("E9:J28")
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
End If
    
Next ws

   
    End Sub

 

[Michael M]

It might be easier to clear the formatting on each sheet, then reapply it after the sort (via the macro, of course)???......especially if there isn't much of it !!

 

[Peter_SSs]

Try applying the colour by Conditional Formatting instead of direct manual formatting. For example, the red cell below is formatted with the simple Conditional Formatting formula shown.

Excel Workbook
	E	F	G	H	I	J
9	19	9	4	19	5	10
10	17	10	2	12	2	6
11	10	8	19	10	7	5
12	19	15	20	15	6	5
13	7	19	17	2	11	19
14	13	20	10	16	10	8
15	9	18	13	14	18	16
16	6	16	15	5	9	7
17	9	16	7	20	14	15
18	20	15	15	11	5	8
19	17	13	6	16	9	2
20	20	18	18	2	10	8
21	8	12	4	20	7	9
22	6	12	8	8	9	7
23	12	7	4	6	11	5
24	2	15	17	5	9	16
25	15	18	14	19	11	11
26	9	3	6	14	15	3
27	17	10	2	11	12	13
28	8	16	18	3	17	12
sort keep format

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F10	1. / Formula is =1	Abc

After using your code to sort, the sheet now looks like this:

Excel Workbook
	E	F	G	H	I	J
9	17	13	6	16	9	2
10	9	3	6	14	15	3
11	10	8	19	10	7	5
12	12	7	4	6	11	5
13	19	15	20	15	6	5
14	17	10	2	12	2	6
15	6	16	15	5	9	7
16	6	12	8	8	9	7
17	13	20	10	16	10	8
18	20	15	15	11	5	8
19	20	18	18	2	10	8
20	8	12	4	20	7	9
21	19	9	4	19	5	10
22	15	18	14	19	11	11
23	8	16	18	3	17	12
24	17	10	2	11	12	13
25	9	16	7	20	14	15
26	2	15	17	5	9	16
27	9	18	13	14	18	16
28	7	19	17	2	11	19
sort keep format

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F10	1. / Formula is =1	Abc

 

[scott_86_]

Hi, thanks for your replies.

After Peter's response, I'm now thinking conditional formatting might work.

Is it possible to have a 'range of cells' with the rule:
- If a cell is not blank (text, numbers), it will have no fill.
- If a cell is blank, it will have a manual fill colour format.

Thanks again.

 

[Peter_SSs]

Is it possible to have a 'range of cells' with the rule:

Yes, but the CF would still have to be applied directly to the relevant cells - and the following does not make sense to me.

- If a cell is not blank (text, numbers), it will have no fill.
- If a cell is blank, it will have a manual fill colour format.

Taking a smaller example as shown below, your description above indicates to me that you would colour F10:I10 and G13:J13 as they are blank.

Book1
	E	F	G	H	I	J
9	3	7	1	6	2	7
10	3					2
11	5	6	3	8	7	7
12	1	4	6	5	9	9
13	3	6
14	2	3	3	3	9	5
sort keep format (2)

You said you wanted to sort the cells but keep the formatting where it is. The result would be as shown below. However, now the coloured cells are not empty. Is that what you want?

Book1
	E	F	G	H	I	J
9	3					2
10	2	3	3	3	9	5
11	3	7	1	6	2	7
12	5	6	3	8	7	7
13	1	4	6	5	9	9
14	3	6
sort keep format (2)

 

[scott_86_]

Peter,

After a bit of trial and error I got it to work. Thanks everyone for your help!

The pathway I used was:

• Home
• Conditioning Formatting
• Highlight Cell Rules
• More Rules
  • Format only cells with:
  • No Blanks
  • Format
    • *Format as required...*

 

[Peter_SSs]

Glad you got something that does what you want.