How to apply Macro to a cell?

**Twaddy** · 08-01-2008, 06:33 PM

Hi

Apologies firstly for cross posting, I wasn't aware of the implications however after reading the rules etc on VBA Noob's post I now have a better understanding so thank you.

I'm looking for a macro to apply to a range of cells which will be updated manually by entering either "A,P,L or E in a particular cell which will then change the colour of the cell and text to either green (A), orange (P), red (L), or yellow (E).

I know how to record the macro so that the cell and text change colour but I don't know how to trigger the macro by entering the letter in the cell.

Thanks in advance for any help

Greg

**VBA Noob** · 08-02-2008, 03:23 AM

See attached. Change colour index to the shade you require

Code used

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("A1:A40")) Is Nothing Then
    Select Case UCase(Target.Value)
        Case "A": icol = 4
        Case "P": icol = 44
        Case "L": icol = 3
        Case "E": icol = 36
        Case Else
        icol = 0
    End Select
Target.Interior.ColorIndex = icol
End If
End Sub

VBA Noob

**Twaddy** · 08-02-2008, 02:40 PM

Hi

Many thanks for your reply and code, works great, I hadn't thought about using a drop down list but it works well.
My initial intention was just to key the letter into the cell, would it be difficult if I wanted to apply code so the colour is triggered by keying the letter?

Can you also advise how I'd change the code so that the text colour matches the cell colour so I end up with a cell which is one colour and I can't see the text letter?

Thanks again

Twaddy

**VBA Noob** · 08-02-2008, 02:45 PM

Just remove the data validation and try

Amended code

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("A1:A40")) Is Nothing Then

Application.ScreenUpdating = False

    Select Case UCase(Target.Value)
        Case "A": icol = 4
        Case "P": icol = 44
        Case "L": icol = 3
        Case "E": icol = 36
        Case Else
        icol = 0
    End Select
    With Target
        .Interior.ColorIndex = icol
        .Font.ColorIndex = icol
    End With
End If

Application.ScreenUpdating = True

End Sub

VBA Noob

**Twaddy** · 08-02-2008, 03:50 PM

Thank you VBA Noob, works a treat, just as I wanted.

I also need to apply something similar to a range of cells where the text is populated automatically from the following formula =IF(E742<1,"N/A",IF(E742<2,"RTW 1",IF(E742<3,"RTW 2",IF(E742<4,"RTW 3",IF(E742<99,"Well Being")))))

I need "RTW 1" to be green, "RTW 2" to be Yellow, "RTW 3" to be Orange and "Well Being" to be red.

I've tried amending the code you wrote however it isn't working, not sure if i'm doing something wrong or whether it's down to the text being populated from a formula.

Sorry to be a pain but could you advise please, struggling to get my head round it

Cheers

**VBA Noob** · 08-02-2008, 04:01 PM

You would need a Worksheet Calculate event as a formula not a change calling the event.
Something like

Private Sub Worksheet_Calculate()
Dim icol As Long
Dim c as Range

For Each c In Range("A1:A40")
Application.ScreenUpdating = False

    Select Case UCase(c.Value)
        Case "A": icol = 4
        Case "P": icol = 44
        Case "L": icol = 3
        Case "E": icol = 36
        Case Else
        icol = 0
    End Select
    With c
        .Interior.ColorIndex = icol
        .Font.ColorIndex = icol
    End With
Next c
Application.ScreenUpdating = True

End Sub

VBA Noob

**Twaddy** · 08-02-2008, 04:28 PM

I've amend the code so it works for RTW 1, 2 and 3, changing the colour of the cell as required however "Well Being" isn't changing colour. Below is my amended code, would you mind looking over it and advise if there's something which would prevent "Well Being" changing the cell colour?

HTML Code:

Thanks

How to apply Macro to a cell?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions