Excel - Changing cell backgroud colour 1

[DAmoss]

Hi,

Can anyone show me how to change a cells background colour using vba.

I want to be able to loop through a give range of values and alter the background colour depending on the percentage value in that cell:

eg.
1 - 25 = green background
26 - 50 = blue background
51 - 75 = red background
76 - 100 = purple background
100+ = .....

Cheers
Don

 

[mikej336]

Range("C3").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With


Uncle Mike

 

[SkipVought]

Hi,

Did you use your macro recorder as suggested?

Please post your code.

Skip,

_[red][/red]

 

[Bong]

            If (cells(i,j)<= 25) Then
                With c1.Interior
                     .ColorIndex = vbGreen
                     .Pattern = xlSolid
                End With
            End If

_________________
Bob Rashkin

 

[SkipVought]

for each r in range("given_range")
    With r.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
next

Skip,

_[red][/red]

 

[DAmoss]

Skip, I didn't use the macro recorder, only just logged in to see these answers, thanks to everyone for all your help.

I'll let you know how I get on.

 

[duGly]

Don,

Here is a code example that will accomplish what you're trying to do:

Private Sub SetColour()

    Select Case CInt([xlInput])
        Case 1 To 25
            [xlInput].Interior.Color = vbGreen
        Case 26 To 50
            [xlInput].Interior.Color = vbBlue
        Case 51 To 75
            [xlInput].Interior.Color = vbRed
        Case 76 To 100
            [xlInput].Interior.Color = vbMagenta
        Case Else
            [xlInput].Interior.Color = vbYellow
    End Select

End Sub

This example assumes that you have a cell named [xlInput]. (You can name it whatever you want.) Alternately, you could refer to the cell by its address (i.e. Range("B3"), but this is less precise and prone to error if rows or columns are added or deleted.

Alternately, you could use the RGB to set specific colors, rather than the limited list of color constants available (vbGreen, vbBlue, etc.). For example, this code sets the cell color to green:

[xlInput].Interior.Color = RGB(0, 128, 0)

Or if you want to use specific colors from your color pallette, use the ColorIndex property rather than the Color property. For example:

[xlInput].Interior.ColorIndex = 10

Hope this helps!

[purple]— Artificial intelligence is no match for natural stupidity.[/purple]

 

[duGly]

Whoops! I forgot that you were wanting to loop through a range of cells. Here's my previous example, but now it loops through each cell in a range called "xlInput":

Private Sub SetColour()
    Dim rngMe As Range

    For Each rngMe In Range("xlInput").Cells
        Select Case CInt(rngMe)
            Case 1 To 25
                rngMe.Interior.ColorIndex = 10
            Case 26 To 50
                rngMe.Interior.Color = vbBlue
            Case 51 To 75
                rngMe.Interior.Color = vbRed
            Case 76 To 100
                rngMe.Interior.Color = vbMagenta
            Case Else
                rngMe.Interior.Color = vbYellow
        End Select
    Next    ' rngMe

End Sub

[purple]— Artificial intelligence is no match for natural stupidity.[/purple]

 

[DAmoss]

Thanks for all your help, I have two little questions left. I can now set the cell background to a different colour, but ...

How do I set it back to its default background colour?
What is the default cell colour ?

Cheers

 

[duGly]

vbWhite or xlNone.

[purple]— Artificial intelligence is no match for natural stupidity.[/purple]