VB Code to change cell colour by entering a word

Hi there,

VBA Noob was kind enough to help me out with a VB code some time back which worked perfectly at the time for it's intended purpose which I've now tried to adapt but I'm having difficulty with it and wondered if somebody would mind taking a look to see what I may be doing wrong.

The original code used just single letters to change the cell colour however I now need the cell colour to change depending on what word is typed into the cell

This is the code I'm trying to use but it ain't working, it works if I just use the capital letter of each word but not the whole word as I need.

HTML Code: 

If it's really simple then I apologise however I have no working knowledge of how to write code, as may be apparent.

Any help would be much appreciated

Cheers

Twaddy

What happens? What doesn't work? Which cell(s) are you modifying?

I've just tested each colour in column E and it seems fine.

Regards

Best of both worlds:

Please Login or Register  to view this content.

Regards

Hello all

Thanks for your replies

Frustratingly the VB works at home on Office 2010 however in work on 97-2003 version it doesn't work.

Macro's are enabled and I've added a test macro at work and that works fine

If I enter any of the specified words anywhere in the range E4:N18, such as Gold, the cell contains the text but remians colourless.

I'm stumped!

I had a similar problem and the following worked for me.
Open file in Excel 2010 and save it as "Excel 97-2003" workbook (*.xls).
Then open this .xls file in older version of excel.

hth
Ajay

Works for me in 2003 and 2007.

I did add the code to a 2007 workbook where macros were disabled (although I hadn't realised) and I got the effect you describe ... that is, input the text but don't get the colour.

I only realised when I tried to enable events, thinking that might be the problem, only to be told that macros were disabled.

Regards

Regards

Yeah Macro's are enabled in 2010 at home and works fine

When I saved it at home I saved as 97-2003 file before emailing to work so that should be fine

Macros are definitely enabled in work on 97-2003 too so I'm flumoxed!

I'd be inclined to set up a new Excel 2003 workbook and just copy and paste the code into it.

In other words, start from scratch. If that works, it's something to do with the transition between the two formats.

Regards

Hi

With the help of a colleague in work we've got it sorted

This is the code we used which has now worked, the only difference I think being that we've taken out the and at the start and end of the code as per my original code, not sure why they were there, what they do or even whether they were the problem but it's now working

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icol As Long
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E4:N18")) Is Nothing Then

Application.ScreenUpdating = False

Select Case UCase(Target.Value)
Case "SILVER": icol = 15
Case "BRONZE": icol = 53
Case "AMBER": icol = 45
Case "RED": icol = 3
Case "GOLD": icol = 6
Case "CVP": icol = 1
Case Else
icol = 0
End Select
With Target
.Interior.ColorIndex = icol
End With
End If

Application.ScreenUpdating = True

End Sub

Thanks very much to everyone for your help

Twaddy

For some reason, you had the italics start and end tags ... as you can see in your last update.

I'm surprised it compiled with those in ... I had to delete them when I copied your code.

I had assumed that they were just put there for presentation in your original post.

Anyway, result. Thanks for letting us know.

Regards