A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » New Users to Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I change cell color in excel based on text input?



 
 
Thread Tools Display Modes
  #1  
Old May 7th 08, 09:16 PM posted to microsoft.public.excel.newusers
giffjr13
external usenet poster
 
Posts: 1
Default 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?
Ads
  #2  
Old May 7th 08, 10:10 PM posted to microsoft.public.excel.newusers
Gord Dibben
external usenet poster
 
Posts: 22,912
Default How do I change cell color in excel based on text input?

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?


  #3  
Old January 12th 10, 07:45 PM posted to microsoft.public.excel.newusers
Chris Lagasse
external usenet poster
 
Posts: 1
Default conditional format for multiple values from separate sheets

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
  #4  
Old January 12th 10, 08:01 PM posted to microsoft.public.excel.newusers
Bernard Liengme[_2_]
external usenet poster
 
Posts: 563
Default conditional format for multiple values from separate sheets

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to change color in cell when there is input TSK Excel Worksheet Functions 6 May 8th 08 12:30 AM
Excel: Syntax to change cell color based on color of another cell davew18 Excel Worksheet Functions 1 January 4th 07 01:24 PM
How do I change fill color in excel based on data in the cell Jason Southco Excel Worksheet Functions 1 September 29th 05 04:56 PM
How do I change the format of a cell based on what I input? Husker87 Excel Worksheet Functions 8 August 19th 05 10:45 PM
How do you change font color based on the value input? 1 = . mojeta Excel Discussion (Misc queries) 1 July 15th 05 07:17 PM


All times are GMT +1. The time now is 02:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.