From: Jack Sons on 1 Jun 2010 19:47 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
From: Project Mangler on 2 Jun 2010 15:22 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" <sonsindenhaag(a)gmail.com> wrote in message news:1D44DE3B-7A86-4E01-A8A2-9FDAEBDB7C4C(a)microsoft.com... > 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
From: Jack Sons on 3 Jun 2010 08:21 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" <sonsindenhaag(a)gmail.com> wrote in message > news:1D44DE3B-7A86-4E01-A8A2-9FDAEBDB7C4C(a)microsoft.com... > > 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 > > > . >
From: Project Mangler on 3 Jun 2010 15:15 Hi Jack, "Jack Sons" <sonsindenhaag(a)gmail.com> wrote in message news:6B37517B-7353-4400-94B6-0A692CCEBC1E(a)microsoft.com... > 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!
From: Project Mangler on 3 Jun 2010 15:38 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" <dblack(a)ntlworld.com> wrote in message news:eE8ZKE1ALHA.1892(a)TK2MSFTNGP05.phx.gbl... > Hi Jack, > > > "Jack Sons" <sonsindenhaag(a)gmail.com> wrote in message > news:6B37517B-7353-4400-94B6-0A692CCEBC1E(a)microsoft.com... > > 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! > >
|
Next
|
Last
Pages: 1 2 Prev: Custom color scheme Next: Can insert digital ink into Excel cell or a cell comment? |