Including Cell Formats with Bubble Sort

[Strat121]

I searched and couldn't find anything with regards to formatting in Excel, but if I have a simple bubble sort:

If Cell1 > Cell2
Temp = Cell1
Cell1 = Cell2
Cell2 = Temp
End If

Is there any way to shift over the cell formatting, specifically both background color and font color, using variables?

 

[PHV]

Why not simply use the native sort ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Strat121]

I've got the actual program shifting multiple sets of data on multiple pages, as well as series collections in graphs. Originally it wasn't a problem until I added the different cell formats based on different conditions. I could re-write it using the native sort, which I suspect I'll have to do anyways, but before I do I was wondering if there was a way to shift the cell formatting.

 

[SkipVought]

"I could re-write it using the native sort..."

Why bother "writing" sort code. Turn on your macro recorder and have at it.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mscallisto]

what exactly are you trying to accomplish?

from your thread title:
"Including Cell Formats with Bubble Sort"

and your question:
"Is there any way to shift over the cell formatting, specifically both background color and font color, using variables?"

I'm still not certain I understand what you need done.

 

[mintjulep]

Font color is range("a1").Font.Color

Background color is range("a1").interior.colorindex

 

[HughLerwill]

Sub sort()

With ActiveSheet
If .Cells(1, 1) > .Cells(2, 1) Then
.Cells(10, 1) = Cells(1, 1)
.Cells(1, 1) = .Cells(2, 1)
.Cells(2, 1) = Cells(10, 1)
End If
End With

End Sub

 

[HughLerwill]

Sorry about that; I dos'nt work

 

[xlbo]

Surely just more lines of code to move the formats over:

If Cell1 > Cell2
Temp = Cell1
TempNumFormat = Cell2.Numberformat
TempTextCol = Cell2.Font.Colorindex
TempBGCol = Cell2.Interior.Colorindex
Cell1 = Cell2
Cell1.Numberformat = TempNumFormat
Cell1.Font.Colorindex = TempTextCol
Cell1.Interior.colorindex = TempBGCol
Cell2 = Temp
End If

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

Just store required properties (mintjulep's post as starting point). Each cell is a range, you can get its Borders, Interior, Font objects/collections and pick their settings. When sorting together with values swap indexes, finally apply sorted formatting.

combo

 

[Strat121]

Thanks all. Got it taken care of. I was having problems at first assigning the formats to variables, but it's working now.