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 Tex
Private Sub Worksheet_Change(ByVal Target As Range
Set r = Range("A1:A20"
If Intersect(Target, r) Is Nothing The
Exit Su
End I
On Error GoTo Endi
Application.EnableEvents = Fals
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
icolor =
For i = LBound(vals) To UBound(vals
If rr.Value = vals(i) The
icolor = nums(i
End I
Nex
If icolor <> 0 The
rr.Interior.ColorIndex = icolo
End I
Nex
Endit
Application.EnableEvents = Tru
End Su
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 t
30 CF's in a cell
http://www.xldynamic.com/source/xld.CFPlus.Download.htm
Gord Dibben MS Excel MV
Previous Posts In This Thread:
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?
Try this event code.
Try this event code
Option Compare Tex
Private Sub Worksheet_Change(ByVal Target As Range
Set r = Range("A1:A20"
If Intersect(Target, r) Is Nothing The
Exit Su
End I
On Error GoTo Endi
Application.EnableEvents = Fals
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
icolor =
For i = LBound(vals) To UBound(vals
If rr.Value = vals(i) The
icolor = nums(i
End I
Nex
If icolor <> 0 The
rr.Interior.ColorIndex = icolo
End I
Nex
Endit
Application.EnableEvents = Tru
End Su
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 t
30 CF's in a cell
http://www.xldynamic.com/source/xld.CFPlus.Download.htm
Gord Dibben MS Excel MV
Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Stored Procedures And Cursors
http://www.eggheadcafe.com/tutorial...9-2acf9a818060/sql-server-stored-procedu.aspx