Change a color depending on a cells value

jjsanders

New Member
Joined
Oct 16, 2006
Messages
36
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:
Code:
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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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.
Code:
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
 

jjsanders

New Member
Joined
Oct 16, 2006
Messages
36
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.
Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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
Code:
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
Code:
Case "T5", "t5"
you can add more items
Case "T5", "t5", "bla"
 

jjsanders

New Member
Joined
Oct 16, 2006
Messages
36
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
Code:
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:
Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

change "T5" to Range("T5")
Code:
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)
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you're WELCOME !!
the little example you gave enlightened a lot the problem: think about to provide an example from start

enjoy the Board :biggrin:
 

Forum statistics

Threads
1,141,624
Messages
5,707,466
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top