Applying conditinal formatting to sorted results

[LWhite]

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

 

[Jim Cone]

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"
<[email protected]>
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

 

[ryguy7272]

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--