random %

[oddworld]

hi does anyone know how i could do the following
i have multable colour name criteria in column a ie

red/green/orange in column a
what i am trying to do is in column b apply a percentage breakup of the 3 colours to column b based on a percentage

look in coumn a "red/green/orange" count how may times this parameter occours and then apply the following percetages to column b.
red,14% - green between 14% and 34% and orange >35%

so if red/green/orange occurs 20 times in column a the break in column b would be red recorded 3 times and green would be recorded 7 times and oranges would be recoreded 13 times as per the percentages(approx)

any ideas

should this be in a case statement i am not sure?

 

[Dufus]

oddworld,

Maybe it's just late here but, I can't figure out if the colors are combined as three in one cell or if each cell only holds one color?

Dufus

 

[oddworld]

re

hi each cell in column a has


red/green/orange
red/green/orange
red/green/orange
red/green/orange
red/green/orange
red/green/orange
red/green/orange
red/green/orange

 

[Dufus]

Would three conditional formats work? Try this for conditional format number 1 for the entire range and set the color to red background:

=IF(ROW(A1)<=ROUND(COUNTIF($A$1:$A$30,"red/green/orange")*0.14,0),TRUE,FALSE)

Dufus

 

[Derek]

Hi there
You can try this (adapt to suit). It is set up for the 7 colors of the rainbow. It will recognise any combination / duplication of those 7 colors.


Sub CountColors()
' Macro recorded 20/09/2006 by DEREK
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str4 As String
Dim str5 As String
Dim str6 As String
Dim str7 As String

Dim Red As Long
Dim Orange As Long
Dim Yellow As Long
Dim Green As Long
Dim Blue As Long
Dim Indigo As Long
Dim Violet As Long

Red = 0
Orange = 0
Yellow = 0
Green = 0
Blue = 0
Indigo = 0
Violet = 0

str1 = "red"
str2 = "orange"
str3 = "yellow"
str4 = "green"
str5 = "blue"
str6 = "indigo"
str7 = "violet"

For Each cell In Range("A1", Range("A65536").End(xlUp))
Red = Red + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str1, ""))) / 3
Orange = Orange + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str2, ""))) / 6
Yellow = Yellow + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str3, ""))) / 6
Green = Green + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str4, ""))) / 5
Blue = Blue + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str5, ""))) / 4
Indigo = Indigo + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str6, ""))) / 6
Violet = Violet + (Len(cell) - Len(WorksheetFunction.Substitute(cell, str7, ""))) / 6
Next

[b1].Value = "Red = " & Red
[b2].Value = "Orange = " & Orange
[b3].Value = "Yellow = " & Yellow
[b4].Value = "Green = " & Green
[b5].Value = "Blue = " & Blue
[b6].Value = "Indigo = " & Indigo
[b7].Value = "Violet = " & Violet

End Sub

regards
Derek