Erdin? E. Ka
03-29-2007, 07:38 PM
Hi everyone,
I wrote a U.D.F. about color info (interior or font) of a cell.
But if a range has no color then my U.D.F. returning 0 ( zero ). But i want that; if user want to get the name of the color information in English: U.D.F. must returns "No Color", in Turksih selection U.D.F. must return: "Renk Yok".
But i can't solved this problem. :think:
The codes like below:
Function COLOR(CeLL As Range, Language As Byte, Return_Only_The_Index_Number As Byte, _
InteriorColor_Or_FontColor As Byte) As Variant
'HELP AND DESCRIPTIONS:
'CeLL : Which Cell You want to Analysis: A6
'Language : for Turkish Language use 1 , for English Language 2
'Return_Only_The_Index_Number : If This argument = 0 thu UDF Returns the ColorIndex Number.
'InteriorColor_Or_FontColor : If you input 1 for this argument: UDF retuns you the INTERIOR
'color, If you input 2 for this argument: UDF retuns you the FONT color
'Not: Her bir arg?mana mutlaka bir değer girilmelidir. Yani =COLOR(A6;1) Gibi sadece iki
'değeri vererek form?l yazmayınız.
'
'Example1: Write This Formula For Learn INTERIOR Color of A6 Cell in Turkish :
'=COLOR(A6;1;1;1)
'Example2: Write This Formula For Learn INTERIOR Color of A6 Cell in English :
'=COLOR(A6;2;1;1)
'Example3: Write This Formula For Learn FONT Color of A6 Cell in English: =COLOR(A6;2;1;2)
'Example4: Write This Formula For Learn The ColorIndex Number of the FONT Color of A6 Cell:
'=COLOR(A6;2;0;2)
'Example5: Write This Formula For Learn The ColorIndex Number of the INTERIOR Color of A6 Cell:
'=COLOR(A6;2;0;1)
'You must write some data for all arguments. For example: If you write like =COLOR(A6;1)
'then UDF returns #VALUE!. So, please don't write absent UDF arguments.
'
Application.Volatile
On Error GoTo End_Of_The_Function
Dim No As Byte
Dim Result As String
If InteriorColor_Or_FontColor = 1 Then
No = CeLL.Interior.ColorIndex
ElseIf InteriorColor_Or_FontColor = 2 Then
No = CeLL.Font.ColorIndex
End If
If Return_Only_The_Index_Number <> 0 Then
If Language = 1 Then
Select Case No
Case Is = 1: Result = "Siyah"
Case Is = 2: Result = "Beyaz"
Case Is = 3: Result = "Kırmızı"
Case Is = 4: Result = "Parlak Yeşil"
Case Is = 5: Result = "Mavi"
Case Is = 6: Result = "Sarı"
Case Is = 7: Result = "Pembe"
Case Is = 8: Result = "Turkuaz"
Case Is = 9: Result = "Koyu Kırmızı"
Case Is = 10: Result = "Yeşil"
Case Is = 11: Result = "Koyu Mavi"
Case Is = 12: Result = "Koyu Sarı"
Case Is = 13: Result = "Mor"
Case Is = 14: Result = "Deniz Mavisi"
Case Is = 15: Result = "Gri %25"
Case Is = 16: Result = "Gri %50"
Case Is = 17: Result = "Koyu Soluk Mavi"
Case Is = 18: Result = "Erik Rengi 2"
Case Is = 19: Result = "?ok A?ık Sarı"
Case Is = 20: Result = "?ok A?ık G?k Mavisi"
Case Is = 21: Result = "Koyu Mor"
Case Is = 22: Result = "Somon"
Case Is = 23: Result = "Mavi 2"
Case Is = 24: Result = "A?ık İndigo"
Case Is = 25: Result = "Lacivert"
Case Is = 26: Result = "Pembe 2"
Case Is = 27: Result = "Sarı 2"
Case Is = 28: Result = "Turkuaz 2"
Case Is = 29: Result = "?ok Koyu Mor"
Case Is = 30: Result = "Kiremit"
Case Is = 31: Result = "A?ık Petrol Mavisi"
Case Is = 32: Result = "Mavi 3"
Case Is = 33: Result = "G?k Mavisi"
Case Is = 34: Result = "A?ık Turkuaz"
Case Is = 35: Result = "A?ık Yeşil"
Case Is = 36: Result = "A?ık Sarı"
Case Is = 37: Result = "Soluk Mavi"
Case Is = 38: Result = "G?l"
Case Is = 39: Result = "A?ık Eflatun"
Case Is = 40: Result = "Tan"
Case Is = 41: Result = "A?ık Mavi"
Case Is = 42: Result = "A?ık Deniz Mavisi"
Case Is = 43: Result = "Limon Rengi"
Case Is = 44: Result = "Altın"
Case Is = 45: Result = "A?ık Turuncu"
Case Is = 46: Result = "Turuncu"
Case Is = 47: Result = "Gri-Mavi"
Case Is = 48: Result = "Gri %40"
Case Is = 49: Result = "Koyu Deniz Mavisi"
Case Is = 50: Result = "Deniz Yeşili"
Case Is = 51: Result = "Koyu Yeşil"
Case Is = 52: Result = "Zeytin Yeşili"
Case Is = 53: Result = "Kahverengi"
Case Is = 54: Result = "Erik Rengi"
Case Is = 55: Result = "İndigo"
Case Is = 56: Result = "Gri %80"
End Select
ElseIf Language = 2 Then
Select Case No
Case Is = 1: Result = "Black"
Case Is = 2: Result = "White"
Case Is = 3: Result = "Red"
Case Is = 4: Result = "Bright Green"
Case Is = 5: Result = "Blue"
Case Is = 6: Result = "Yellow"
Case Is = 7: Result = "Pink"
Case Is = 8: Result = "Turquoise"
Case Is = 9: Result = "Dark Red"
Case Is = 10: Result = "Green"
Case Is = 11: Result = "Dark Blue"
Case Is = 12: Result = "Dark Yellow"
Case Is = 13: Result = "Violet"
Case Is = 14: Result = "Teal"
Case Is = 15: Result = "Gray-25%"
Case Is = 16: Result = "Gray-50%"
Case Is = 17: Result = "Dark Pale Blue"
Case Is = 18: Result = "Plum 2"
Case Is = 19: Result = "Very Light Yellow"
Case Is = 20: Result = "Very Light Sky Blue"
Case Is = 21: Result = "Dark Violet"
Case Is = 22: Result = "Somon"
Case Is = 23: Result = "Blue 2"
Case Is = 24: Result = "Light Indigo"
Case Is = 25: Result = "Dark Blue 2"
Case Is = 26: Result = "Pink 2"
Case Is = 27: Result = "Yellow 2"
Case Is = 28: Result = "Turquoise 2"
Case Is = 29: Result = "Very Dark Violet"
Case Is = 30: Result = "Clay Roofing Tile"
Case Is = 31: Result = "Light Petroil Blue"
Case Is = 32: Result = "Blue 3"
Case Is = 33: Result = "Sky Blue"
Case Is = 34: Result = "Light Turquoise"
Case Is = 35: Result = "Light Green"
Case Is = 36: Result = "Light Yellow"
Case Is = 37: Result = "Pale Blue"
Case Is = 38: Result = "Rose"
Case Is = 39: Result = "Lavender"
Case Is = 40: Result = "Tan"
Case Is = 41: Result = "Light Blue"
Case Is = 42: Result = "Aqua"
Case Is = 43: Result = "Lime"
Case Is = 44: Result = "Gold"
Case Is = 45: Result = "Light Orange"
Case Is = 46: Result = "Orange"
Case Is = 47: Result = "Blue-Gray"
Case Is = 48: Result = "Gray-40%"
Case Is = 49: Result = "Dark Teal"
Case Is = 50: Result = "Sea Green"
Case Is = 51: Result = "Dark Green"
Case Is = 52: Result = "Olive Green"
Case Is = 53: Result = "Brown"
Case Is = 54: Result = "Plum"
Case Is = 55: Result = "Indigo"
Case Is = 56: Result = "Gray-80%"
End Select
End If
COLOR = Result
Else
COLOR = No
End If
End_Of_The_Function:
End Function
Also i posted the sample worksheet of U.D.F.
Any ideas?:think:
Thanks in advance.:friends:
Edited 30-Mar-07 by geekgirlau. Reason: insert line breaks
I wrote a U.D.F. about color info (interior or font) of a cell.
But if a range has no color then my U.D.F. returning 0 ( zero ). But i want that; if user want to get the name of the color information in English: U.D.F. must returns "No Color", in Turksih selection U.D.F. must return: "Renk Yok".
But i can't solved this problem. :think:
The codes like below:
Function COLOR(CeLL As Range, Language As Byte, Return_Only_The_Index_Number As Byte, _
InteriorColor_Or_FontColor As Byte) As Variant
'HELP AND DESCRIPTIONS:
'CeLL : Which Cell You want to Analysis: A6
'Language : for Turkish Language use 1 , for English Language 2
'Return_Only_The_Index_Number : If This argument = 0 thu UDF Returns the ColorIndex Number.
'InteriorColor_Or_FontColor : If you input 1 for this argument: UDF retuns you the INTERIOR
'color, If you input 2 for this argument: UDF retuns you the FONT color
'Not: Her bir arg?mana mutlaka bir değer girilmelidir. Yani =COLOR(A6;1) Gibi sadece iki
'değeri vererek form?l yazmayınız.
'
'Example1: Write This Formula For Learn INTERIOR Color of A6 Cell in Turkish :
'=COLOR(A6;1;1;1)
'Example2: Write This Formula For Learn INTERIOR Color of A6 Cell in English :
'=COLOR(A6;2;1;1)
'Example3: Write This Formula For Learn FONT Color of A6 Cell in English: =COLOR(A6;2;1;2)
'Example4: Write This Formula For Learn The ColorIndex Number of the FONT Color of A6 Cell:
'=COLOR(A6;2;0;2)
'Example5: Write This Formula For Learn The ColorIndex Number of the INTERIOR Color of A6 Cell:
'=COLOR(A6;2;0;1)
'You must write some data for all arguments. For example: If you write like =COLOR(A6;1)
'then UDF returns #VALUE!. So, please don't write absent UDF arguments.
'
Application.Volatile
On Error GoTo End_Of_The_Function
Dim No As Byte
Dim Result As String
If InteriorColor_Or_FontColor = 1 Then
No = CeLL.Interior.ColorIndex
ElseIf InteriorColor_Or_FontColor = 2 Then
No = CeLL.Font.ColorIndex
End If
If Return_Only_The_Index_Number <> 0 Then
If Language = 1 Then
Select Case No
Case Is = 1: Result = "Siyah"
Case Is = 2: Result = "Beyaz"
Case Is = 3: Result = "Kırmızı"
Case Is = 4: Result = "Parlak Yeşil"
Case Is = 5: Result = "Mavi"
Case Is = 6: Result = "Sarı"
Case Is = 7: Result = "Pembe"
Case Is = 8: Result = "Turkuaz"
Case Is = 9: Result = "Koyu Kırmızı"
Case Is = 10: Result = "Yeşil"
Case Is = 11: Result = "Koyu Mavi"
Case Is = 12: Result = "Koyu Sarı"
Case Is = 13: Result = "Mor"
Case Is = 14: Result = "Deniz Mavisi"
Case Is = 15: Result = "Gri %25"
Case Is = 16: Result = "Gri %50"
Case Is = 17: Result = "Koyu Soluk Mavi"
Case Is = 18: Result = "Erik Rengi 2"
Case Is = 19: Result = "?ok A?ık Sarı"
Case Is = 20: Result = "?ok A?ık G?k Mavisi"
Case Is = 21: Result = "Koyu Mor"
Case Is = 22: Result = "Somon"
Case Is = 23: Result = "Mavi 2"
Case Is = 24: Result = "A?ık İndigo"
Case Is = 25: Result = "Lacivert"
Case Is = 26: Result = "Pembe 2"
Case Is = 27: Result = "Sarı 2"
Case Is = 28: Result = "Turkuaz 2"
Case Is = 29: Result = "?ok Koyu Mor"
Case Is = 30: Result = "Kiremit"
Case Is = 31: Result = "A?ık Petrol Mavisi"
Case Is = 32: Result = "Mavi 3"
Case Is = 33: Result = "G?k Mavisi"
Case Is = 34: Result = "A?ık Turkuaz"
Case Is = 35: Result = "A?ık Yeşil"
Case Is = 36: Result = "A?ık Sarı"
Case Is = 37: Result = "Soluk Mavi"
Case Is = 38: Result = "G?l"
Case Is = 39: Result = "A?ık Eflatun"
Case Is = 40: Result = "Tan"
Case Is = 41: Result = "A?ık Mavi"
Case Is = 42: Result = "A?ık Deniz Mavisi"
Case Is = 43: Result = "Limon Rengi"
Case Is = 44: Result = "Altın"
Case Is = 45: Result = "A?ık Turuncu"
Case Is = 46: Result = "Turuncu"
Case Is = 47: Result = "Gri-Mavi"
Case Is = 48: Result = "Gri %40"
Case Is = 49: Result = "Koyu Deniz Mavisi"
Case Is = 50: Result = "Deniz Yeşili"
Case Is = 51: Result = "Koyu Yeşil"
Case Is = 52: Result = "Zeytin Yeşili"
Case Is = 53: Result = "Kahverengi"
Case Is = 54: Result = "Erik Rengi"
Case Is = 55: Result = "İndigo"
Case Is = 56: Result = "Gri %80"
End Select
ElseIf Language = 2 Then
Select Case No
Case Is = 1: Result = "Black"
Case Is = 2: Result = "White"
Case Is = 3: Result = "Red"
Case Is = 4: Result = "Bright Green"
Case Is = 5: Result = "Blue"
Case Is = 6: Result = "Yellow"
Case Is = 7: Result = "Pink"
Case Is = 8: Result = "Turquoise"
Case Is = 9: Result = "Dark Red"
Case Is = 10: Result = "Green"
Case Is = 11: Result = "Dark Blue"
Case Is = 12: Result = "Dark Yellow"
Case Is = 13: Result = "Violet"
Case Is = 14: Result = "Teal"
Case Is = 15: Result = "Gray-25%"
Case Is = 16: Result = "Gray-50%"
Case Is = 17: Result = "Dark Pale Blue"
Case Is = 18: Result = "Plum 2"
Case Is = 19: Result = "Very Light Yellow"
Case Is = 20: Result = "Very Light Sky Blue"
Case Is = 21: Result = "Dark Violet"
Case Is = 22: Result = "Somon"
Case Is = 23: Result = "Blue 2"
Case Is = 24: Result = "Light Indigo"
Case Is = 25: Result = "Dark Blue 2"
Case Is = 26: Result = "Pink 2"
Case Is = 27: Result = "Yellow 2"
Case Is = 28: Result = "Turquoise 2"
Case Is = 29: Result = "Very Dark Violet"
Case Is = 30: Result = "Clay Roofing Tile"
Case Is = 31: Result = "Light Petroil Blue"
Case Is = 32: Result = "Blue 3"
Case Is = 33: Result = "Sky Blue"
Case Is = 34: Result = "Light Turquoise"
Case Is = 35: Result = "Light Green"
Case Is = 36: Result = "Light Yellow"
Case Is = 37: Result = "Pale Blue"
Case Is = 38: Result = "Rose"
Case Is = 39: Result = "Lavender"
Case Is = 40: Result = "Tan"
Case Is = 41: Result = "Light Blue"
Case Is = 42: Result = "Aqua"
Case Is = 43: Result = "Lime"
Case Is = 44: Result = "Gold"
Case Is = 45: Result = "Light Orange"
Case Is = 46: Result = "Orange"
Case Is = 47: Result = "Blue-Gray"
Case Is = 48: Result = "Gray-40%"
Case Is = 49: Result = "Dark Teal"
Case Is = 50: Result = "Sea Green"
Case Is = 51: Result = "Dark Green"
Case Is = 52: Result = "Olive Green"
Case Is = 53: Result = "Brown"
Case Is = 54: Result = "Plum"
Case Is = 55: Result = "Indigo"
Case Is = 56: Result = "Gray-80%"
End Select
End If
COLOR = Result
Else
COLOR = No
End If
End_Of_The_Function:
End Function
Also i posted the sample worksheet of U.D.F.
Any ideas?:think:
Thanks in advance.:friends:
Edited 30-Mar-07 by geekgirlau. Reason: insert line breaks