Hello i have entered the code exactly as you say and it
doesnt do anything, im not sure what im doing wrong.
Gord Dibben wrote:
DanFor 10 numbers you would have to use VBA......event code is best.
14-Feb-07
Da
For 10 numbers you would have to use VBA......event code is best
Private Sub Worksheet_Change(ByVal Target As Range
Dim Num As Lon
Dim rng As Rang
Dim vRngInput As Varian
Set vRngInput = Intersect(Target, Range("A1")
If vRngInput Is Nothing Then Exit Su
For Each rng In vRngInpu
'Determine the colo
Select Case rng.Valu
Case Is = 1: Num = 6 'yello
Case Is = 2: Num = 10 'gree
Case Is = 3: Num = 5 'blu
Case Is = 4: Num = 3 're
Case Is = 5: Num = 46 'orang
'add more cases here as abov
End Selec
'Apply the colo
Range("H1").Interior.ColorIndex = Nu
Next rn
End Su
Just add more Cases and colors using the example above
To see what the colorindexes are visit David McRitchie's sit
http://www.mvps.org/dmcritchie/excel/colors.ht
This code is sheet event code. Right-click on the sheet tab and "View Code
Copy/paste the code into that sheet module
You can add the Cases at that point
Gord Dibben MS Excel MV
On Wed, 14 Feb 2007 12:47:18 -0800, danielocop
Previous Posts In This Thread:
Change cell color based on another cell value
Hi Al
I must be thick headed; I have looked through most of the posts here for
help and some come very close to answering my question, or maybe it???s just me
I have the three conditional formatting settings in cell H1 based on the
value of cell A1. Please note there is no value in H1, no number or letter,
just the pattern color. I was hoping not to / do not want to use VB editor to
create some master code for the work sheet. I just want to use a FORMULA in
H1 to change the cell (H1) color based on the number value in cell A1
Like =IF(A1=5,".ColorIndex = 6",IF(A1=6,".ColorIndex = 7")
(Numbers 1 ??? 10 would each have a different color, 1-red, 2-blue, 3-green,
and so on
Now for the thick headed part, if a formula can not change a cell color is
there ANY other way to do this other than some code sheet, VB editing,
monster in Excel 2003
Thanks for taking the time to look this over, and if I need to wrestle with
the code monster will you all be there to guide me through, PLEASE
Dan
DanFor 10 numbers you would have to use VBA......event code is best.
Da
For 10 numbers you would have to use VBA......event code is best
Private Sub Worksheet_Change(ByVal Target As Range
Dim Num As Lon
Dim rng As Rang
Dim vRngInput As Varian
Set vRngInput = Intersect(Target, Range("A1")
If vRngInput Is Nothing Then Exit Su
For Each rng In vRngInpu
'Determine the colo
Select Case rng.Valu
Case Is = 1: Num = 6 'yello
Case Is = 2: Num = 10 'gree
Case Is = 3: Num = 5 'blu
Case Is = 4: Num = 3 're
Case Is = 5: Num = 46 'orang
'add more cases here as abov
End Selec
'Apply the colo
Range("H1").Interior.ColorIndex = Nu
Next rn
End Su
Just add more Cases and colors using the example above
To see what the colorindexes are visit David McRitchie's sit
http://www.mvps.org/dmcritchie/excel/colors.ht
This code is sheet event code. Right-click on the sheet tab and "View Code
Copy/paste the code into that sheet module
You can add the Cases at that point
Gord Dibben MS Excel MV
On Wed, 14 Feb 2007 12:47:18 -0800, danielocop
Gord Dibben MS Excel MVPThank you Gord, that works great!
Gord Dibben
MS Excel MV
Thank you Gord, that works great! And the best part is I did not need to
fight the code monster. I just copied your code to the sheet tab as you
explained and vole??? it worked! You would think that would have been the end
of it correct? Wrong! The monster got a hold of me and now is dragging me
down that slippery slope. If you would be willing to help me out and offer
another suggestion I would be grateful?
All on my own I figured out that if I change the last line in your code,
Range("H1").Interior.ColorIndex = Num,- from ???H1??? to ???A1:A40??? (or A:A) I can
get the whole column of cell colors to change based on the number in cell A1.
OR
If I change the SET line of your code ???Set vRngInput = Intersect(Target,
Range("A1"))??? from ???A1??? to ???A1:A40??? or(???A:A???),- then the cell A1 color will
change based on the last number entered in any cell in the column.
AND
Combining the two code changes noted above the whole column changes with
each number interned in any cell in the column!
NOW
What if I need each individual cell (in the range of A1:A40) to change color
based on the number within each individual cell. And I want 40 colors to pick
from, let???s just use color #1 - #40, this is what I tried so far.
By adding Case lines to the Case section of your code to add 40 colors like
this-
Case Is = 1: Num = 1
Case Is = 2: Num = 2
Case Is = 3: Num = 3
And so on all the way to 40, I can get all the colors to work fine.
For the individual cells I thought if I changed the 2nd line of your code
from ???Dim rng As Range??? to ???Dim rng As each??? that would allow each cell to
have its own color.
BUT as you know that did not work, in fact the monster won???t even let me
make that change. I used CellFormat in place of ???rng??? and the monster made me
change a bunch of other lines of code and before long nothing looked like the
code you gave me so I just exited out and saved your original code in another
work sheet. I copied the total code and pasting it 40 times, changing the
???Range??? in each set of codes to ???A2, A3 and so on??? but that only make the
monster even angrier.
So here I am fighting the code monster UUUGGG!
Can you please suggest the proper changes to your code?
Thank you for your support I know how frustrating it must be for you at
times dealing with meatheads??? like me!
Dan
:
To have each cell its own color depending upon value in the cell.........
To have each cell its own color depending upon value in the cell.........
Note the change to Target.Interior.ColorIndex = Num
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A1:A40"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
'add more cases here as above
End Select
'Apply the color
Target.Interior.ColorIndex = Num
Next rng
End Sub
For 40 colors and cases it may be easier this way...........
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Dim rr As Range
Set r = Range("A1:A40")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("1", "2", "3", "4", "5", "6", "7", "8", "9")
nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
icolor = nums(i)
End If
Next
If icolor > 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub
Gord
On Thu, 15 Feb 2007 09:10:29 -0800, danielocope
Thank you Gord Dibben the two codes work great for me.
Thank you Gord Dibben the two codes work great for me.
I will never understand it but it works
THANK YOU VERY MUCH
Dan
:
DanI just barely understand it so welcome to the club<g>I like the second one
Dan
I just barely understand it so welcome to the club<g>
I like the second one that uses the Arrays.
Not so much typing.
One of the real coders can probably come up with a method of building an array
without typing all those numbers.
Gord
On Thu, 15 Feb 2007 14:05:05 -0800, danielocope
Submitted via EggHeadCafe - Software Developer Portal of Choice
Server Side Processing in ADO.NET/WCF Data Services
http://www.eggheadcafe.com/tutorial...f-4f6f92a76585/server-side-processing-in.aspx