I want the cell format (meaning color of the cell) to change based upon the
text entered. I have used up the conditioal formatting. It only allows three
formats, and i require more. I am inquiring if this can be made into a
formula for a cell to have the color change upon certain text phrases?

Try this event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A20")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo Endit
Application.EnableEvents = False
vals = Array("Cat", "Dog", "Gopher", "Hyena", "Ibex", "Lynx", _
"Ocelot", "Skunk", "Tiger", "Yak")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor < 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
Endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".. Copy/paste to that sheet module.

Edit then Alt + q to freturn to the Excel window.

As an alternative.............Bob Phillips has a CFPLUS add-in that allows up to
30 CF's in a cell.

http://www.xldynamic.com/source/xld.....Download.html


Gord Dibben MS Excel MVP

On Wed, 7 May 2008 13:16:02 -0700, giffjr13
wrote:

I want the cell format (meaning color of the cell) to change based upon the
text entered. I have used up the conditioal formatting. It only allows three
formats, and i require more. I am inquiring if this can be made into a
formula for a cell to have the color change upon certain text phrases?

I'm trying to color cell backgrounds based on the value of an input cell(1) equaling the value of another cell(2) where cell(2) is derived from another sheet such as =SHEET2!$L2.

If a cell in the range A1:A4 equals A5 then color that cell with a specific color. Values of A1:A4 would be text strings, such as "B" or "A B", and the value in A5 would be a text string from a second sheet in the workbook such as =SHEET2!$L2 where the value is "B" or "A B".



Gord Dibben wrote:

Try this event code.
07-May-08

Try this event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A20")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo Endit
Application.EnableEvents = False
vals = Array("Cat", "Dog", "Gopher", "Hyena", "Ibex", "Lynx", _
"Ocelot", "Skunk", "Tiger", "Yak")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor < 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
Endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".. Copy/paste to that sheet module.

Edit then Alt + q to freturn to the Excel window.

As an alternative.............Bob Phillips has a CFPLUS add-in that allows up to
30 CF's in a cell.

http://www.xldynamic.com/source/xld.....Download.html


Gord Dibben MS Excel MVP

On Wed, 7 May 2008 13:16:02 -0700, giffjr13
wrote:

Previous Posts In This Thread:

On Wednesday, May 07, 2008 4:16 PM
giffjr1 wrote:

How do I change cell color in excel based on text input?
I want the cell format (meaning color of the cell) to change based upon the
text entered. I have used up the conditioal formatting. It only allows three
formats, and i require more. I am inquiring if this can be made into a
formula for a cell to have the color change upon certain text phrases?

On Wednesday, May 07, 2008 5:10 PM
Gord Dibben wrote:

Try this event code.
Try this event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A20")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo Endit
Application.EnableEvents = False
vals = Array("Cat", "Dog", "Gopher", "Hyena", "Ibex", "Lynx", _
"Ocelot", "Skunk", "Tiger", "Yak")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor < 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
Endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".. Copy/paste to that sheet module.

Edit then Alt + q to freturn to the Excel window.

As an alternative.............Bob Phillips has a CFPLUS add-in that allows up to
30 CF's in a cell.

http://www.xldynamic.com/source/xld.....Download.html


Gord Dibben MS Excel MVP

On Wed, 7 May 2008 13:16:02 -0700, giffjr13
wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Stored Procedures And Cursors
http://www.eggheadcafe.com/tutorials...d-procedu.aspx

Select A1:A4 and in Conditional Formatting use Formula Is (in Excel 2007:
New Rule / based on formula), with =A1=$A$5 and set the formatting you want.

How A5 gets it value is not relevant here
best wishes

"Chris Lagasse" wrote in message ...
I'm trying to color cell backgrounds based on the value of an input
cell(1) equaling the value of another cell(2) where cell(2) is derived
from another sheet such as =SHEET2!$L2.

If a cell in the range A1:A4 equals A5 then color that cell with a
specific color. Values of A1:A4 would be text strings, such as "B" or "A
B", and the value in A5 would be a text string from a second sheet in the
workbook such as =SHEET2!$L2 where the value is "B" or "A B".



Gord Dibben wrote:

Try this event code.
07-May-08

Try this event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A20")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo Endit
Application.EnableEvents = False
vals = Array("Cat", "Dog", "Gopher", "Hyena", "Ibex", "Lynx", _
"Ocelot", "Skunk", "Tiger", "Yak")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor < 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
Endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".. Copy/paste to that sheet
module.

Edit then Alt + q to freturn to the Excel window.

As an alternative.............Bob Phillips has a CFPLUS add-in that allows
up to
30 CF's in a cell.

http://www.xldynamic.com/source/xld.....Download.html


Gord Dibben MS Excel MVP

On Wed, 7 May 2008 13:16:02 -0700, giffjr13

wrote:

Previous Posts In This Thread:

On Wednesday, May 07, 2008 4:16 PM
giffjr1 wrote:

How do I change cell color in excel based on text input?
I want the cell format (meaning color of the cell) to change based upon
the
text entered. I have used up the conditioal formatting. It only allows
three
formats, and i require more. I am inquiring if this can be made into a
formula for a cell to have the color change upon certain text phrases?

On Wednesday, May 07, 2008 5:10 PM
Gord Dibben wrote:

Try this event code.
Try this event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A20")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo Endit
Application.EnableEvents = False
vals = Array("Cat", "Dog", "Gopher", "Hyena", "Ibex", "Lynx", _
"Ocelot", "Skunk", "Tiger", "Yak")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor < 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
Endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".. Copy/paste to that sheet
module.

Edit then Alt + q to freturn to the Excel window.

As an alternative.............Bob Phillips has a CFPLUS add-in that allows
up to
30 CF's in a cell.

http://www.xldynamic.com/source/xld.....Download.html


Gord Dibben MS Excel MVP

On Wed, 7 May 2008 13:16:02 -0700, giffjr13

wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Stored Procedures And Cursors
http://www.eggheadcafe.com/tutorials...d-procedu.aspx

Yes, you can change the cell color in Excel based on text input by using a formula. Here are the steps to do it:

1. Select the cell or range of cells that you want to apply the formatting to.
2. Click on the "Conditional Formatting" button in the "Home" tab of the ribbon.
3. Select "New Rule" from the drop-down menu.
4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
5. In the "Format values where this formula is true" field, enter the formula that will determine the cell color. For example, if you want the cell to turn red when the text "Error" is entered, you would enter the formula
   Formula:

   =A1="Error" 

   (assuming the text is in cell A1).
6. Click on the "Format" button to choose the color you want the cell to turn when the formula is true.
7. Click "OK" to close the "Format Cells" dialog box.
8. Click "OK" again to close the "New Formatting Rule" dialog box.

Now, whenever the text "Error" is entered in cell A1, the cell will turn red. You can create additional rules for other text phrases and colors by repeating the above steps with different formulas and formatting options.