Format of CELL

Skip to first unread message

Rahul Bahl

Apr 20, 1999, 3:00:00 AM4/20/99
I have a problem that I need to resolve. If I have a selection of colors
that I can set the background of a cell to.
For example:- Red, Green, Blue, Magenta, Yellow or White
I want to be able to automatically state in the adjacent cell or another
cell in a seperate
worksheet what color the shaded cell is. There would only be a
possibility of the above colors.


Column A B
1 *
* This cell is shaded blue, therefore B1 automatically detects it as
being blue and fills B1 with the text "BLUE"

Please Help

Apr 21, 1999, 3:00:00 AM4/21/99
You can do this easily in VBA.

open your Visual Basic Editor (Alt+F11)
add a new module to your project ( from the VBE menu select [Insert]/[Module]
and paste in the following code
Const Red = vbRed
Const Blue = vbBlue
Const Green = vbGreen
Const Yellow = vbYellow

Sub PasteColors()
Dim intColor As Integer
For i = 0 To 15
Cells(i + 5, 5).Interior.Color = Choose(Int(Rnd() * 4) + 1, Red, Blue,
Green, Yellow)
End Sub

Function ShowColor(rCell As Range) As String
Dim s As String
Select Case rCell.Interior.Color
Case Red: s = "Red"
Case Blue: s = "Blue"
Case Green: s = "Green"
Case Yellow: s = "Yellow"
Case Else: s = ""
End Select
ShowColor = s
End Function

The subroutine is just a tester - it simply randomly colors 15 cells in a
column to one of 4 basic colors. There are of course more - but you can do the
rest for yourself !

run this sub. Now got to the spreadsheet and in a cell to the right of the
column of colors - ie cell F5 type in this: =ShowColor(E5) then copy it down
to F20 You'll see that the function checks the color of the cell reference
passed to it (ie cells in the nect left column) and returns its color.

Hope that this gives you some ideas

In article <>,

-----------== Posted via Deja News, The Discussion Network ==---------- Search, Read, Discuss, or Start Your Own

Reply all
Reply to author
0 new messages