Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying conditinal formatting to sorted results
Hello,
I have a list of products that is being sorted by more than a few columns. I am wanting to apply different cell colors to the rows depending on the values in the sort. The data is a list of toys. most of the sort columns are Y or N answers. My data is structured with the following column headers. Product group item number description has metal has plastic has paint has stickers has synthetic fiber So what we have is data that looks something like this. 001XXX, 001001, plastic truck - red, Y, Y, N, Y, N 001XXX, 001002, plastic truck - blue,Y, Y, N, Y, N 001XXX, 001011, metal truck - red, Y, Y, Y, Y, N 001XXX, 001012, metal truck - blue, Y, Y, Y, Y, N 002XXX, 002010, doll - blonde, N, Y, N, N, Y 002XXX, 002011, doll - red, N, Y, N, N, Y 002XXX, 002012, doll - brown, N, Y, N, N, Y 002XXX, 002110, doll - blonde, Y, Y, N, N, Y 002XXX, 002111, doll - red, Y, Y, N, N, Y 002XXX, 002112, doll - brown, Y, Y, N, N, Y 003XXX, 003003, ball - large, N, Y, N, N, N, N, Y 003XXX, 003013, ball - medium, N, Y, N, N, N 003XXX, 003023, ball - small, N, Y, N, N, N All trucks have the same group because they are trucks, but I want to change the color of the rows the metal trucks are on because they have paint. A sort of the entire sheet will structure things so that the Y/ N columns are together but it is still easy to miss a difference in the attributes. Since my real data has around 6000 rows I would like to do this with a loop and automatically change the color of a row to another color. So the code should go through the group 001XXX and change the last two rows to blue. Then start to do the same thing to the 002XXX group. The sheet is sorted left to right but ignores part number and description. Can anyone here help me with this please? LWhite |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying conditinal formatting to sorted results
Conditionally formatted colors will not sort with data. And for what it's worth, neither will cell borders. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (download the free trial of Shade Data Rows) "LWhite" wrote in message Hello, I have a list of products that is being sorted by more than a few columns. I am wanting to apply different cell colors to the rows depending on the values in the sort. The data is a list of toys. most of the sort columns are Y or N answers. My data is structured with the following column headers. Product group item number description has metal has plastic has paint has stickers has synthetic fiber So what we have is data that looks something like this. 001XXX, 001001, plastic truck - red, Y, Y, N, Y, N 001XXX, 001002, plastic truck - blue,Y, Y, N, Y, N 001XXX, 001011, metal truck - red, Y, Y, Y, Y, N 001XXX, 001012, metal truck - blue, Y, Y, Y, Y, N 002XXX, 002010, doll - blonde, N, Y, N, N, Y 002XXX, 002011, doll - red, N, Y, N, N, Y 002XXX, 002012, doll - brown, N, Y, N, N, Y 002XXX, 002110, doll - blonde, Y, Y, N, N, Y 002XXX, 002111, doll - red, Y, Y, N, N, Y 002XXX, 002112, doll - brown, Y, Y, N, N, Y 003XXX, 003003, ball - large, N, Y, N, N, N, N, Y 003XXX, 003013, ball - medium, N, Y, N, N, N 003XXX, 003023, ball - small, N, Y, N, N, N All trucks have the same group because they are trucks, but I want to change the color of the rows the metal trucks are on because they have paint. A sort of the entire sheet will structure things so that the Y/ N columns are together but it is still easy to miss a difference in the attributes. Since my real data has around 6000 rows I would like to do this with a loop and automatically change the color of a row to another color. So the code should go through the group 001XXX and change the last two rows to blue. Then start to do the same thing to the 002XXX group. The sheet is sorted left to right but ignores part number and description. Can anyone here help me with this please? LWhite |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying conditinal formatting to sorted results
I don't fully understand conditional formatting, but I think the color is
applied on top of the cell, instead of within the cell, so you can't really measure what is in the cell. Nevertheless, you can 'evaluate' the conditional formatting with the following code, and then you can sort based on the results. Function CountOfCF(InRange As Range, _ Optional Condition As Integer = -1) As Long Dim Count As Long Dim Rng As Range Dim FCNum As Integer For Each Rng In InRange.Cells FCNum = ActiveCondition(Rng) If FCNum 0 Then If Condition = -1 Or Condition = FCNum Then Count = Count + 1 End If End If Next Rng CountOfCF = Count End Function Function ActiveCondition(Rng As Range) As Integer Dim Ndx As Long Dim FC As FormatCondition Dim Temp As Variant Dim Temp2 As Variant If Rng.FormatConditions.Count = 0 Then ActiveCondition = 0 Else For Ndx = 1 To Rng.FormatConditions.Count Set FC = Rng.FormatConditions(Ndx) Select Case FC.Type Case xlCellValue Select Case FC.Operator Case xlBetween Temp = GetStrippedValue(FC.Formula1) Temp2 = GetStrippedValue(FC.Formula2) If IsNumeric(Temp) Then If CDbl(Rng.Value) = CDbl(FC.Formula1) And _ CDbl(Rng.Value) <= CDbl(FC.Formula2) Then ActiveCondition = Ndx Exit Function End If Else If Rng.Value = Temp And _ Rng.Value <= Temp2 Then ActiveCondition = Ndx Exit Function End If End If Case xlGreater Temp = GetStrippedValue(FC.Formula1) If IsNumeric(Temp) Then If CDbl(Rng.Value) CDbl(FC.Formula1) Then ActiveCondition = Ndx Exit Function End If Else If Rng.Value Temp Then ActiveCondition = Ndx Exit Function End If End If Case xlEqual Temp = GetStrippedValue(FC.Formula1) If IsNumeric(Temp) Then If CDbl(Rng.Value) = CDbl(FC.Formula1) Then ActiveCondition = Ndx Exit Function End If Else If Temp = Rng.Value Then ActiveCondition = Ndx Exit Function End If End If Case xlGreaterEqual Temp = GetStrippedValue(FC.Formula1) If IsNumeric(Temp) Then If CDbl(Rng.Value) = CDbl(FC.Formula1) Then ActiveCondition = Ndx Exit Function End If Else If Rng.Value = Temp Then ActiveCondition = Ndx Exit Function End If End If Case xlLess Temp = GetStrippedValue(FC.Formula1) If IsNumeric(Temp) Then If CDbl(Rng.Value) < CDbl(FC.Formula1) Then ActiveCondition = Ndx Exit Function End If Else If Rng.Value < Temp Then ActiveCondition = Ndx Exit Function End If End If Case xlLessEqual Temp = GetStrippedValue(FC.Formula1) If IsNumeric(Temp) Then If CDbl(Rng.Value) <= CDbl(FC.Formula1) Then ActiveCondition = Ndx Exit Function End If Else If Rng.Value <= Temp Then ActiveCondition = Ndx Exit Function End If End If Case xlNotEqual Temp = GetStrippedValue(FC.Formula1) If IsNumeric(Temp) Then If CDbl(Rng.Value) < CDbl(FC.Formula1) Then ActiveCondition = Ndx Exit Function End If Else If Temp < Rng.Value Then ActiveCondition = Ndx Exit Function End If End If Case xlNotBetween Temp = GetStrippedValue(FC.Formula1) Temp2 = GetStrippedValue(FC.Formula2) If IsNumeric(Temp) Then If Not (CDbl(Rng.Value) <= CDbl(FC.Formula1)) And _ (CDbl(Rng.Value) = CDbl(FC.Formula2)) Then ActiveCondition = Ndx Exit Function End If Else If Not Rng.Value <= Temp And _ Rng.Value = Temp2 Then ActiveCondition = Ndx Exit Function End If End If Case Else Debug.Print "UNKNOWN OPERATOR" End Select Case xlExpression If Application.Evaluate(FC.Formula1) Then ActiveCondition = Ndx Exit Function End If Case Else Debug.Print "UNKNOWN TYPE" End Select Next Ndx End If ActiveCondition = 0 End Function Function GetStrippedValue(CF As String) As String Dim Temp As String If InStr(1, CF, "=", vbTextCompare) Then Temp = Mid(CF, 3, Len(CF) - 3) If Left(Temp, 1) = "=" Then Temp = Mid(Temp, 2) End If Else Temp = CF End If GetStrippedValue = Temp End Function I forgot where I found this code, so if you have lots of questions you may be out of luck. Just try it; it should work for you. Call the function with this: =ActiveCondition(A1) Regards, Ryan-- -- RyGuy "Jim Cone" wrote: Conditionally formatted colors will not sort with data. And for what it's worth, neither will cell borders. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (download the free trial of Shade Data Rows) "LWhite" wrote in message Hello, I have a list of products that is being sorted by more than a few columns. I am wanting to apply different cell colors to the rows depending on the values in the sort. The data is a list of toys. most of the sort columns are Y or N answers. My data is structured with the following column headers. Product group item number description has metal has plastic has paint has stickers has synthetic fiber So what we have is data that looks something like this. 001XXX, 001001, plastic truck - red, Y, Y, N, Y, N 001XXX, 001002, plastic truck - blue,Y, Y, N, Y, N 001XXX, 001011, metal truck - red, Y, Y, Y, Y, N 001XXX, 001012, metal truck - blue, Y, Y, Y, Y, N 002XXX, 002010, doll - blonde, N, Y, N, N, Y 002XXX, 002011, doll - red, N, Y, N, N, Y 002XXX, 002012, doll - brown, N, Y, N, N, Y 002XXX, 002110, doll - blonde, Y, Y, N, N, Y 002XXX, 002111, doll - red, Y, Y, N, N, Y 002XXX, 002112, doll - brown, Y, Y, N, N, Y 003XXX, 003003, ball - large, N, Y, N, N, N, N, Y 003XXX, 003013, ball - medium, N, Y, N, N, N 003XXX, 003023, ball - small, N, Y, N, N, N All trucks have the same group because they are trucks, but I want to change the color of the rows the metal trucks are on because they have paint. A sort of the entire sheet will structure things so that the Y/ N columns are together but it is still easy to miss a difference in the attributes. Since my real data has around 6000 rows I would like to do this with a loop and automatically change the color of a row to another color. So the code should go through the group 001XXX and change the last two rows to blue. Then start to do the same thing to the 002XXX group. The sheet is sorted left to right but ignores part number and description. Can anyone here help me with this please? LWhite |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditinal Formatting | New Users to Excel | |||
Conditinal Formatting | Excel Discussion (Misc queries) | |||
Conditinal Formatting | Excel Programming | |||
Conditinal formatting - AND function | Excel Worksheet Functions | |||
Font size in conditinal formatting | Excel Discussion (Misc queries) |