A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

VBA sort range with certain fontcolor



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 12:47 AM posted to microsoft.public.excel.misc
Jack Sons[_2_]
external usenet poster
 
Posts: 4
Default VBA sort range with certain fontcolor

Hi all,

Ik column K there will be a continuous range of which all cells have a
certain fontcolor, the font color code is, say, 5. The range can be 1 cell up
to 30 (or so), perhaps even zero. There are no cells in column K outside that
range that also have font color code 5.
While executing the code it is not (yet) known what the rownumbers of the
first and last cells in that range are.

I need code that will sort (descending) the following range:

from row of first cell (in column K) with font color code 5 and column A
to
row of last cell (in column K) with font color code 5 and column AV. Of
course no sorting if none of the cells in column K have font color code 5.

I can't figure out the code, so I need your assistance, which will be
appreciated very much.

--
Jack Sons
The Netherlands
  #2  
Old June 2nd, 2010, 08:22 PM posted to microsoft.public.excel.misc
Project Mangler
external usenet poster
 
Posts: 6
Default VBA sort range with certain fontcolor

Hi Jack,

Option Explicit

Sub colorCell()
Dim A As Range
Dim B As Range
Dim c As Range
Dim D As Range

Set A = Cells(1, 11).End(xlDown)
Set B = Cells(Rows.Count, 11).End(xlUp)

For Each c In Range(A, B)
If c.Font.ColorIndex = 5 And D Is Nothing Then
Set D = c
ElseIf c.Font.ColorIndex = 5 And Not D Is Nothing Then
Set D = Application.Union(Range(D.Address), Range(c.Address))
End If
Next c
If D Is Nothing Then Exit Sub
Worksheets("Sheet1").Range(D.Offset(0, -10), D.Offset(0, 37)).Sort _
Key1:=Worksheets("Sheet1").Columns("K"), order1:=xlDescending, _
Header:=xlNo

End Sub


"Jack Sons" wrote in message
...
Hi all,

Ik column K there will be a continuous range of which all cells have a
certain fontcolor, the font color code is, say, 5. The range can be 1 cell

up
to 30 (or so), perhaps even zero. There are no cells in column K outside

that
range that also have font color code 5.
While executing the code it is not (yet) known what the rownumbers of the
first and last cells in that range are.

I need code that will sort (descending) the following range:

from row of first cell (in column K) with font color code 5 and column A
to
row of last cell (in column K) with font color code 5 and column AV. Of
course no sorting if none of the cells in column K have font color code 5.

I can't figure out the code, so I need your assistance, which will be
appreciated very much.

--
Jack Sons
The Netherlands



  #3  
Old June 3rd, 2010, 01:21 PM posted to microsoft.public.excel.misc
Jack Sons[_2_]
external usenet poster
 
Posts: 4
Default VBA sort range with certain fontcolor

PM,

Thanks for the code. I tried it but it does nothing. As far as my abilities
go I checked the working of the code. I saw that it starts wit setting A and
B both to equal the content of the last cell in column K. Stepping from there
through the code I see that the first c has black font color and D equals
"nothing". After elseif follows end if and then I see that "next c" only
results in stepping to "if D = nothing ..." while (of course) D is still
nothing so the sub exits. I think the code misses something.
For your information: in the case that I checked the first 9 cells (1 header
and 8 data) are all with black font (font code 1). Then follow 13 cells with
font code 5 and after that 3 cells with again black font.

Please be so kind as to make the necessary changes in the code (I can't
figure them out) and - if it is not to time consuming - please explain how
the code works. A thousand thanks in advance.
--
Jack Sons
The Netherlands


"Project Mangler" wrote:

Hi Jack,

Option Explicit

Sub colorCell()
Dim A As Range
Dim B As Range
Dim c As Range
Dim D As Range

Set A = Cells(1, 11).End(xlDown)
Set B = Cells(Rows.Count, 11).End(xlUp)

For Each c In Range(A, B)
If c.Font.ColorIndex = 5 And D Is Nothing Then
Set D = c
ElseIf c.Font.ColorIndex = 5 And Not D Is Nothing Then
Set D = Application.Union(Range(D.Address), Range(c.Address))
End If
Next c
If D Is Nothing Then Exit Sub
Worksheets("Sheet1").Range(D.Offset(0, -10), D.Offset(0, 37)).Sort _
Key1:=Worksheets("Sheet1").Columns("K"), order1:=xlDescending, _
Header:=xlNo

End Sub


"Jack Sons" wrote in message
...
Hi all,

Ik column K there will be a continuous range of which all cells have a
certain fontcolor, the font color code is, say, 5. The range can be 1 cell

up
to 30 (or so), perhaps even zero. There are no cells in column K outside

that
range that also have font color code 5.
While executing the code it is not (yet) known what the rownumbers of the
first and last cells in that range are.

I need code that will sort (descending) the following range:

from row of first cell (in column K) with font color code 5 and column A
to
row of last cell (in column K) with font color code 5 and column AV. Of
course no sorting if none of the cells in column K have font color code 5.

I can't figure out the code, so I need your assistance, which will be
appreciated very much.

--
Jack Sons
The Netherlands



.

  #4  
Old June 3rd, 2010, 08:15 PM posted to microsoft.public.excel.misc
Project Mangler
external usenet poster
 
Posts: 6
Default VBA sort range with certain fontcolor

Hi Jack,


"Jack Sons" wrote in message
...
PM,

Thanks for the code. I tried it but it does nothing.


Well that is disappointing.

As far as my abilities
go I checked the working of the code. I saw that it starts wit setting A

and
B both to equal the content of the last cell in column K.


I'm not clear why
Set A = Cells(1, 11).End(xlDown)
would ever find the last cell in an occupied column unless that column only
had one populated cell. If that is what is happening then the rest of the
code will never work.

You could substitute the line above with
Set A = Range("K1").End(xlDown)
but really it shouldn't make any difference.

I think the code misses something.


All I can tell you is that it works here in excel 2003. I don't post
untested code but perhaps I misread your original post.
My test sheet is populated from A3 to AV22 with a mix of numbers and text in
alternate columns (1 - 22) for numbers, (a - t) for text with one number or
letter per cell in ascending order down the column.
K8 - K13 have blue font (colorindex 5), all others are black font.

The code correctly identifies the start and end of the occupied range in
column K and sorts A8 - AV13 in descending order. The biggest flaw in the
code that I have identified is that it will only sort the first range of
coloured cells encountered should there be more than one block of them.

For your information: in the case that I checked the first 9 cells (1

header
and 8 data) are all with black font (font code 1). Then follow 13 cells

with
font code 5 and after that 3 cells with again black font.


I assume that this is in Column K as indicated in your original post.

Please be so kind as to make the necessary changes in the code (I can't
figure them out) and - if it is not to time consuming - please explain how
the code works. A thousand thanks in advance.


I suggest that you try a repost in microsoft.public.excel.programming. Thre
are much more capable coders in there than me. Perhaps one of them will
correct my attempt.

Good luck!


  #5  
Old June 3rd, 2010, 08:38 PM posted to microsoft.public.excel.misc
Project Mangler
external usenet poster
 
Posts: 6
Default VBA sort range with certain fontcolor

Hi Jack,

Apologies, dumb error on my part:

I assume that your populated range begins in K1?

If so change Set A = Cells(1, 11).End(xlDown)
to
Set A = Range("K1")

HTH


"Project Mangler" wrote in message
...
Hi Jack,


"Jack Sons" wrote in message
...
PM,

Thanks for the code. I tried it but it does nothing.


Well that is disappointing.

As far as my abilities
go I checked the working of the code. I saw that it starts wit setting A

and
B both to equal the content of the last cell in column K.


I'm not clear why
Set A = Cells(1, 11).End(xlDown)
would ever find the last cell in an occupied column unless that column

only
had one populated cell. If that is what is happening then the rest of the
code will never work.

You could substitute the line above with
Set A = Range("K1").End(xlDown)
but really it shouldn't make any difference.

I think the code misses something.


All I can tell you is that it works here in excel 2003. I don't post
untested code but perhaps I misread your original post.
My test sheet is populated from A3 to AV22 with a mix of numbers and text

in
alternate columns (1 - 22) for numbers, (a - t) for text with one number

or
letter per cell in ascending order down the column.
K8 - K13 have blue font (colorindex 5), all others are black font.

The code correctly identifies the start and end of the occupied range in
column K and sorts A8 - AV13 in descending order. The biggest flaw in the
code that I have identified is that it will only sort the first range of
coloured cells encountered should there be more than one block of them.

For your information: in the case that I checked the first 9 cells (1

header
and 8 data) are all with black font (font code 1). Then follow 13 cells

with
font code 5 and after that 3 cells with again black font.


I assume that this is in Column K as indicated in your original post.

Please be so kind as to make the necessary changes in the code (I can't
figure them out) and - if it is not to time consuming - please explain

how
the code works. A thousand thanks in advance.


I suggest that you try a repost in microsoft.public.excel.programming.

Thre
are much more capable coders in there than me. Perhaps one of them will
correct my attempt.

Good luck!




  #6  
Old June 3rd, 2010, 11:32 PM posted to microsoft.public.excel.misc
Jack Sons
external usenet poster
 
Posts: 137
Default VBA sort range with certain fontcolor

PM,

I did like you advised, and now it works like a charm. Marvelous, thank you.

Jack.


"Project Mangler" schreef in bericht
...
Hi Jack,

Apologies, dumb error on my part:

I assume that your populated range begins in K1?

If so change Set A = Cells(1, 11).End(xlDown)
to
Set A = Range("K1")

HTH


"Project Mangler" wrote in message
...
Hi Jack,


"Jack Sons" wrote in message
...
PM,

Thanks for the code. I tried it but it does nothing.


Well that is disappointing.

As far as my abilities
go I checked the working of the code. I saw that it starts wit setting
A

and
B both to equal the content of the last cell in column K.


I'm not clear why
Set A = Cells(1, 11).End(xlDown)
would ever find the last cell in an occupied column unless that column

only
had one populated cell. If that is what is happening then the rest of the
code will never work.

You could substitute the line above with
Set A = Range("K1").End(xlDown)
but really it shouldn't make any difference.

I think the code misses something.


All I can tell you is that it works here in excel 2003. I don't post
untested code but perhaps I misread your original post.
My test sheet is populated from A3 to AV22 with a mix of numbers and text

in
alternate columns (1 - 22) for numbers, (a - t) for text with one number

or
letter per cell in ascending order down the column.
K8 - K13 have blue font (colorindex 5), all others are black font.

The code correctly identifies the start and end of the occupied range in
column K and sorts A8 - AV13 in descending order. The biggest flaw in the
code that I have identified is that it will only sort the first range of
coloured cells encountered should there be more than one block of them.

For your information: in the case that I checked the first 9 cells (1

header
and 8 data) are all with black font (font code 1). Then follow 13 cells

with
font code 5 and after that 3 cells with again black font.


I assume that this is in Column K as indicated in your original post.

Please be so kind as to make the necessary changes in the code (I can't
figure them out) and - if it is not to time consuming - please explain

how
the code works. A thousand thanks in advance.


I suggest that you try a repost in microsoft.public.excel.programming.

Thre
are much more capable coders in there than me. Perhaps one of them will
correct my attempt.

Good luck!






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.