Color Formula

[b13]

Hi Guys,

I need a formula that can translate a color cell to a word. For example, cell A1 is color red, in cell B1 it should write "Late". Or in cell A2 is green so it will write "on time in" in cell B2 and so forth.

Thanks guys,

Hope you can help me with this.

 

[JoeMo]

Unless the colors are determined by conditional formatting you can't do this with a formula. If conditionally formatted, you can use use a formula that is keyed to the condition(s) that set the color. If not conditionally formatted, this can be done with VBA.

 

[ExcelChemist]

I have a solution for you, I made it to only do the first ten cells, you could have it pop up a message box and ask what the last cell you want formatted is, or select the range and have the macro do the selected range. I formatted the cells to the normal red and green and got 3 and 14 and color indices, respectively, you will have to determine what the color index of the colors you used are.

Sub test()
l As Long

For l = 0 To 9
    Cells(l + 1, 1).Select
        With Selection.Interior
            If Selection.Interior.ColorIndex = 3 Then
            ActiveCell.Offset(0, 1).Value = "Late"
            ElseIf Selection.Interior.ColorIndex = 14 Then
            ActiveCell.Offset(0, 1).Value = "On Time"
            End If
        End With
Next l
End Sub

 

[b13]

Hi JoeMo,

Thanks for the reply. For this example, what are the correct conditional format that i can use?Red if it exceed the deadline date, green if same as deadline and orange if date before deadline.

<TABLE style="WIDTH: 437pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=582><COLGROUP><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><COL style="WIDTH: 209pt; mso-width-source: userset; mso-width-alt: 10166" width=278><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: #99ccff; WIDTH: 139pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20 width=185>Deadline Date</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: #99ccff; WIDTH: 209pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=278>Submission Date</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: #99ccff; WIDTH: 89pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=119>STATUS</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: #eeece1; WIDTH: 139pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 height=20 width=185>12/6/2011</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: red; WIDTH: 209pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl67 width=278>12/7/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">LATE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: #eeece1; WIDTH: 139pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 height=20 width=185>12/6/2011</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: red; WIDTH: 209pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl67 width=278>12/7/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">LATE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: #eeece1; WIDTH: 139pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 height=20 width=185>12/6/2011</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: #92d050; WIDTH: 209pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=278>12/6/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">ON TIME</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: #eeece1; WIDTH: 139pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 height=20 width=185>12/6/2011</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: #92d050; WIDTH: 209pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=278>12/6/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">ON TIME</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: #eeece1; WIDTH: 139pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 height=20 width=185>12/6/2011</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: #ffc000; WIDTH: 209pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=278>12/5/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">BEFORE DEADLINE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: #eeece1; WIDTH: 139pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 height=20 width=185>12/6/2011</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: #ffc000; WIDTH: 209pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl68 width=278>12/5/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">BEFORE DEADLINE</TD></TR></TBODY></TABLE>

Or, the status column will show late, on time, and before deadline.

Thanks a lot

 

[b13]

Thanks Excel Chemist but i dont know how to use VBA codes

 

[ExcelChemist]

Sub EnterResultsBasedOnCellColor()
Dim l As Long
Dim rEndRowNum As Integer
Range("B2").Select
rEndRowNum = Selection.End(xlDown).Row
Debug.Print rEndRowNum

For l = 2 To rEndRowNum
    Cells(l, 2).Select
        With Selection.Interior
            If Selection.Interior.ColorIndex = 3 Then
            ActiveCell.Offset(0, 1).Value = "Late"
            ElseIf Selection.Interior.ColorIndex = 14 Then
            ActiveCell.Offset(0, 1).Value = "On Time"
            End If
        End With
Next l
End Sub

 

[ExcelChemist]

Hit Alt + F11 to open up VBA, under insert press module, copy and paste the code into the module. Then if you have the developer tab visible (Excel options, popular, show developer tab in the ribbon) hit macros and hit run and it will go

 

[b13]

Hi excel chemist...it didnt work..huhuhu...

 

[JoeMo]

Try this b13:

Excel Workbook
	A	B	C
1	Deadline	Submission	Status
2	12/6/2011	12/7/2011	LATE
3	12/6/2011	12/6/2011	ON TIME
4	12/6/2011	12/5/2011	BEFORE DEADLINE
Sheet1

Set the conditional formats like this with all data cells in column B selected:
Red fill: =$B2>$A2
Green fill: =$B2=$A2
Yellow fill: =$B2<$A2

 

[b13]

Hi Joemo,

Thanks a lot, it been a great help.