changing the color of each cell in a selection

[lezawang]

Hi
I wrote the code below to change the color of each cell in a selection. Do you think it is good and efficient code? How you would do it yourself. Thank you so much.

Sub myselection()
    Dim xcell As Range
    Dim i As Integer
    i = 1
    For Each xcell In Selection
        xcell.Interior.ColorIndex = i
        i = i + 1
    Next
End Sub

update: I just found that if the selection is big then the code will fail because colorindex will be more than 56. Hence, I will see how you would do it. Thank you

 

[Eric W]

It's fine if that's what you're looking to do. Since there are only 56 color indices, you may want to change one line to

    xcell.Interior.ColorIndex = (i Mod 56) + 1

to prevent errors in case of a large range.

 

[Fluff]

Try

        i = IIf(i = 56, 1, i + 1)

 

[DanteAmor]

Also on the 56 colors:

Sub myselection()
    Dim xcell As Range, i As Long
    i = 1
    For Each xcell In Selection
        i = IIf(i < 56, i + 1, 1)
        xcell.Interior.ColorIndex = i
    Next
End Sub

 

[Kamolga]

I got an error if more than 50 cells

Sub myselection()
Dim xcell As Range
Dim i As Integer: i = 1
    For Each xcell In Selection
        xcell.Interior.ColorIndex = i
        If i = 50 Then
           i = 1
         Else: i = i + 1
        End If
    Next xcell
End Sub

Note that if then you rigt-click the sheet name, select "view code" and paste

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call myselection
End Sub

Selecting cells on that sheet will automatically color them without having to launch the macro manually

 

[DanteAmor]

I got an error if more than 50 cells

Sub myselection()
Dim xcell As Range
Dim i As Integer: i = 1
    For Each xcell In Selection
        xcell.Interior.ColorIndex = i
        If i = 50 Then
           i = 1
         Else: i = i + 1
        End If
    Next xcell
End Sub

Note that if then you rigt-click the sheet name, select "view code" and paste

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call myselection
End Sub

Selecting cells on that sheet will automatically color them without having to launch the macro manually

See post#2 from @Eric W

It's fine if that's what you're looking to do. Since there are only 56 color indices, you may want to change one line to

 

[Eric W]

It's probably worth noting that there are other ways to set a cell's color. You can also use the

xcell.Interior.Color = 12345

property. If you use that, the values can range from 0 to 256^3 - 1. It also makes sense to use the RGB function like this:

xcell.Interior.Color = RGB(10, 200, 40)

where each value in RGB varies from 0 to 255 and denotes the amounts of Red/Green/Blue to use in the mix. If you use the Macro Recorder while changing a cell's color, then look at the code, you'll see that there are other properties you can use to modify the color, like ThemeColor and TintAndShade.