Change a color depending on a cells value

[jjsanders]

Dear all,

I know there is a way to decide the color of a cell depending on the vaule of a cell. Since I am working with Excel 2000 there is only a maximum of 3 preset profiles. But i want more so I found a VBA script on the internet but it doesn't seem to work.

Here is my script:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   If Intersect(Target, Range("H:H")) Is Nothing _
Or Target.Cells.Count > 1 Then Exit Sub
   
   With Target
    Select Case .Value
      Case "T5" To "t5"
          .Interior.ColorIndex = 50
      Case "T6" To "t6"
          .Interior.ColorIndex = 20
      Case "T7" To "t7"
          .Interior.ColorIndex = 10
      Case "T9" To "t9"
          .Interior.ColorIndex = 5
    End Select
   End With


  End Sub

When I change the vaule of a field in the "H" column i want it to change the fill colour. But now when i change the value nothing happens. I tried to restart excel but still nothing happens.
Can Anyone tell me what i do wrong?

 

[erik.van.geit]

Hello, jjsanders,
Welcome to the Board !!!!!

1. did you put this code in the worksheetmodule ?
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE

2.

Case "T5" To "t5"

what do you want to do here: can you tell in plain english, please
looks like a cellreference ?

kind regards,
Erik

 

[erik.van.geit]

perhaps I missed something important

When I change the vaule of a field

a FIELD ?
do you mean a range - several cells ?

 

[jjsanders]

Hello, jjsanders,
Welcome to the Board !!!!!

1. did you put this code in the worksheetmodule ?
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE

2.

Case "T5" To "t5"

what do you want to do here: can you tell in plain english, please
looks like a cellreference ?

kind regards,
Erik

1. Yes this is what i did.
2. All the cells in the H column are a lookup field. The lookup has 4 vaules which are stored in the cells T5 to T9. What i want is, depending on the selected vaule to change the field color.
Does this sound more clear?

Thanks in advance!

 

[erik.van.geit]

can you confirm that column H is changed manually ? (as I understood from your first post)
or are there formulas ? then you need the worksheetcalculate event

for me it is working
changing a value in column H, changes color

let's also be sure the code is really triggered
put this on top

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
MsgBox "Worksheet_Change running"

you should see the box popup

NOTE that this code will only work when changing one single item at a time

REMARK:
although your code is working with "TO" - normally used for numbers
Case "T5" To "t5"
I would use

Case "T5", "t5"

you can add more items
Case "T5", "t5", "bla"

 

[jjsanders]

can you confirm that column H is changed manually ? (as I understood from your first post)
or are there formulas ? then you need the worksheetcalculate event

Yes it is changed manualy.

for me it is working
changing a value in column H, changes color

let's also be sure the code is really triggered
put this on top

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
MsgBox "Worksheet_Change running"

you should see the box popup

I changed the code, the message box also apeard to me, but the color doesn't change.
So i Tried to change the field T5 to it's hard coded value.
Like this:

Case "Blocking"

Like this it does work. So i don't understand why it doesn't work if I use the cell T5.
Thanks so far!

 

[erik.van.geit]

change "T5" to Range("T5")

Case Range("T5")

I was confused by some elements of your posts, else I would have told you earlier

REMARK:
"field" is not the correct term for "cell" or "range" (can be several cells)

 

[jjsanders]

That was it!

Thanx for you quick help.

I will keep your Remark in mind!

 

[erik.van.geit]

you're WELCOME !!
the little example you gave enlightened a lot the problem: think about to provide an example from start

enjoy the Board