Change Cell-Background color into Text

[Kai Tücking]

Hello,

I have a big table which maps our members' presence to dates by using
special cell background color. So for example one color means "is ill
and another means "is present" and so on...
Unfortunately I have to transfer this table to a sql database and
could not find a way to export cell-background colors to a .csv file.

So my question is:

Is there a simple way to change the celltext according to the cell'
background-color in the whole worksheet ?

For example:
cell-background = "none user set color" -> cell-text = ""
cell-background = "green" -> cell-text = "green"
cell-background = "red" -> cell-text = "red"

and so on ...


Thank you for your answers

K. Tückin

 

[jordanctc]

Here is some code that could be made into what you need - the first is
macro to find the number associated with the colors you use and th
second inserts the color name (one cell over) of the background color

Option Explicit

Sub FindColor()
Range("A1").Select
Do
MsgBox "Cell color is " & Selection.Interior.ColorIndex
ActiveCell.Offset(1, 0).Select
Loop Until Selection.Interior.ColorIndex = -4142 'No Fill Color
End Sub

Sub Color2Text()
Range("A1").Select
Do
Select Case Selection.Interior.ColorIndex
Case Is = 1
ActiveCell.Offset(0, 1).Value = "Black"
Case Is = 9
ActiveCell.Offset(0, 1).Value = "Dark Red"
Case Is = 3
ActiveCell.Offset(0, 1).Value = "Red"
Case Is = 7
ActiveCell.Offset(0, 1).Value = "Pink"
Case Is = 38
ActiveCell.Offset(0, 1).Value = "Rose"
' etc, etc...
End Select
ActiveCell.Offset(1, 0).Select
Loop Until Selection.Interior.ColorIndex = -4142
End Sub


Jorda