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