VB Code to change cell fill colour based on searching for key word

[Twin]

I am trying to write a macro that I can run in different worksheets that will search the work sheet for key words and when it finds them changes the fill colour of the cell that the words are in.

Very High: dark Red
High: Red
Medium: Orange
Low: green
Very Low:light green

I am using excel 03 so conditional formatting isn't enough.

Is this possible?

Thanks

 

[VoG]

See if this helps http://www.vbaexpress.com/kb/getarticle.php?kb_id=90

 

[jim may]

Paste the below code into your THISWORKBOOK Module: Reassign the color code numbers
currently assigned below (Lines 2 thru 6) before continuing...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
VH = 3
H = 4
M = 5
L = 6
VL = 7
Set Rng = ActiveSheet.UsedRange
     For Each C In Rng
        Select Case C.Value
        Case vbNullString
            C.Interior.ColorIndex = xlNone
        Case "Very High"
            C.Interior.Color = VH
        Case "High"
            C.Interior.ColorIndex = H
        Case "Medium"
            C.Interior.ColorIndex = M
        Case "Low"
            C.Interior.ColorIndex = L
        Case "Very Low"
            C.Interior.ColorIndex = VL
        Case Else
            C.Interior.ColorIndex = xlNone
        End Select
    Next C
End Sub

 

[jim may]

correction...

Case "Very High"
C.Interior.Color = VH

should be

Case "Very High"
C.Interior.ColorIndex = VH

 

[Twin]

Thanks for the code. It looks like what i am after. Although i am having a problem copying it into a module.

I copy it into a module but I am struggling to get it to runs as when I press the 'play' button it asks for a macro name?

I am quite new to macros and VB so i think i am missing something really simple..

Any advice would be appreicated

Thanks

 

[jim may]

The Revised code goes into your THISWORKBOOK module of the VBE.
From your Spreadsheet, press ALT+F11 (This will OPEN the VBE)
In the left-hand Pane look for THISWORKBOOK << Double Click on it !!
A code window should Open to the RIGHT.

Paste the revised code into this Window.

The Macro RUNS AUTOMATICALLY, in ALL shetts of your WB when you make
a change to any cells.

HTH,

Jim

 

[Twin]

Thank you. That fixed it. It works great now.