[Solved] VBA: Colour cell depending on Value

    Hi All,


    I am trying to use the following macro to format cells depending on cell value. When I run it I get the error message "Method'Range' of object' Global'Failed.


    Can anyone help.


    Thanks,
    Rennie



    Sub ColourCells()


    Dim rMyCell As Range
    For Each rMyCell In Range("DataRange")

    If rMyCell.Value = "1" Then
    Selection.Interior.ColorIndex = 6
    ElseIf rMyCell.Value = "2" Then
    Selection.Interior.ColorIndex = 7
    ElseIf rMyCell.Value = "3" Then
    Selection.Interior.ColorIndex = 8
    ElseIf rMyCell.Value = "4" Then
    Selection.Interior.ColorIndex = 9
    ElseIf rMyCell.Value = "5" Then
    Selection.Interior.ColorIndex = 10
    ElseIf rMyCell.Value = "6" Then
    Selection.Interior.ColorIndex = 11
    Else
    Selection.Interior.ColorIndex = xlNone
    End If

    Next rMyCell


    End Sub

    Try two things


    Use
    Dim rMyCell
    don't use it as a Range


    Then change all the Selections as follows
    rMyCell.Interior.ColorIndex = 6


    Untested


    HTH

    There are three types of people in this world.
    Those who can count and those who can't.

    Thank you for your reply Neale. I have chenged the macro to the following. I still get the same error message.




    Dim rMyCell


    Sub ColourCells()
    For Each rMyCell In Range("DataRange")

    If rMyCell.Value = "1" Then
    Interior.ColorIndex = 6
    ElseIf rMyCell.Value = "2" Then
    Interior.ColorIndex = 7
    ElseIf rMyCell.Value = "3" Then
    Interior.ColorIndex = 8
    ElseIf rMyCell.Value = "4" Then
    Interior.ColorIndex = 9
    ElseIf rMyCell.Value = "5" Then
    Interior.ColorIndex = 10
    ElseIf rMyCell.Value = "6" Then
    Interior.ColorIndex = 11
    Else
    Interior.ColorIndex = xlNone
    End If

    Next rMyCell


    End Sub

    I think you will need to use
    rMyCell.Interior.ColorIndex = 6


    instead of
    Interior.ColorIndex = 6


    Also try using the sheet name before your Range eg


    Worksheets("Sheetname").Range("DataRange")


    Also check that "DataRange" range is OK - eg it exists and is spelled correctly.



    Also as programming tip have a look at Select Case it does the above split but a bit easier.


    HTH

    There are three types of people in this world.
    Those who can count and those who can't.

    Thank you again Neale. You were right the range should have read "DateRange".


    The following code workes.


    Thank you again for you help.



    Sub ColourCells()
    Sheets("Time").Select
    For Each rMyCell In Range("DateRange")

    If rMyCell.Value = 1 Then
    rMyCell.Interior.ColorIndex = 6
    ElseIf rMyCell.Value = 2 Then
    rMyCell.Interior.ColorIndex = 7
    ElseIf rMyCell.Value = 3 Then
    rMyCell.Interior.ColorIndex = 8
    ElseIf rMyCell.Value = 4 Then
    rMyCell.Interior.ColorIndex = 9
    ElseIf rMyCell.Value = 5 Then
    rMyCell.Interior.ColorIndex = 10
    ElseIf rMyCell.Value = 6 Then
    rMyCell.Interior.ColorIndex = 11
    Else
    rMyCell.Interior.ColorIndex = xlNone
    End If

    Next rMyCell


    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login