Thread: Using VB Code to change cell colour on back of formula

Hi

I have a range of cells which populate the words Gold, Silver, Bronze or Red on the back of a LOOKUP formula which all works fine, I now need a VB Code to be able to change the cell colour depending on which word has been returned.

I've attempted to adapt a previously used code however the cells are remaining white and I'm also getting a Runtime Error 13 which appears to be stopping other code within the sheet working as it should.

This is the code I've tried using, can anyone see any obvious errors?

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

For Each c In Range("af4:af18")
Application.ScreenUpdating = False

    Select Case UCase(c.Value)
        Case "GOLD": icol = 6
        Case "SILVER": icol = 15
        Case "BRONZE": icol = 46
        Case "RED": icol = 3
        Case Else
        icol = 0
    End Select
    With c
        .Interior.ColorIndex = icol
        .Font.ColorIndex = icol
    End With
Next c
Application.ScreenUpdating = True

End Sub

Many thanks

Originally Posted by Marcol

Put your code in the worksheet Change module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icol As Long
    Dim c As Range

    For Each c In Range("af4:af18")

        Select Case UCase(c.Value)
        Case "GOLD": icol = 6
        Case "SILVER": icol = 15
        Case "BRONZE": icol = 46
        Case "RED": icol = 3
        Case Else
            icol = 0
        End Select
        With c
            .Interior.ColorIndex = icol
            .Font.ColorIndex = icol
        End With
    Next c

End Sub

Can't you just use Conditional Formatting in Range("af4:af18")?

Thanks for your reply

I'm getting the following error when I try using the above code

Compile error: Ambigous name detected: Worksheet_Changne

In relation to the conditional formatting question, I should have said I onlt have access to Office 97-2003 at work which only has the option for 3 rules as you'll know

Cheers

Originally Posted by Marcol

Put the code in the resultant VBa window.
If there is already something in the change event, then add your code to it..

Might be a daft question but at what stage would I add the code? If these were the two codes where would the second code blend with the first and do I need to remove any code for them both to work

Thanks for all your help

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 = 46
        Case "AMBER": icol = 44
        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

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icol As Long
    Dim c As Range

    For Each c In Range("af4:af18")

        Select Case UCase(c.Value)
        Case "GOLD": icol = 6
        Case "SILVER": icol = 15
        Case "BRONZE": icol = 46
        Case "RED": icol = 3
        Case Else
            icol = 0
        End Select
        With c
            .Interior.ColorIndex = icol
            .Font.ColorIndex = icol
        End With
    Next c

End Sub

Hi

I've attached a dummy workbook as requested

I'm almost there with this I think but I'm now getting a runtime error when I enter Gold, Silver etc in column E, the Scores in column AF are changing colour as I want although I also want to be able to see the text in column AF

I've added text into cells E4 to E8 which all works fine, this in turn calculates a percentage score in column AD which then populates a status of either Gold, Silver, Bronze or Red in column AF which is the range of cells that I want to apply the code to change colour depending on the score

Hope that all makes sense

Many thanks in advance for any help

Hi snb

Thanks for the code, it works fine for the first part of what I'm trying to achieve however I also need the cells in column AF to change colour depending on the result of the LOOKUP formula

How would I build on your code to do that please?

Cheers

Originally Posted by protonLeah

If Not Intersect(Target, Range("E4:N18", "Af4:af18")) Is Nothing Then

Hi

I've added the above code where I assume to be correct however the cells in AF4:AF18 remain unchanged.

Can you see what is stopping the cells in AF4:AF18 changing?

Many thanks

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", "Af4:af18")) Is Nothing Then

Application.ScreenUpdating = False

    Select Case UCase(Target.Value)
        Case "SILVER": icol = 15
        Case "BRONZE": icol = 45
        Case "AMBER": icol = 44
        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

[QUOTE=Leith Ross;2389268]Hello Twaddy,

NOTE: When a formula calculates, it does not trigger the Worksheet_Change event.

QUOTE]

Hi Leith

Thanks for your reply

So to clarify, it isn't possible to have a change event change the cell colour in e4:n18 based on what is typed into the cell AND that changes af4:af18 automatically depending on the outcome of the LOOKUP formula?

Many thanks

Twaddy