change color on slection with VBA

[behedwin]

How can i change the color of the cells that i select with VBA?

I want to make any selection, then click a button and then the background color of that selection should change.

Got this

If ActiveCell.Interior.ColorIndex = 18 Then
ActiveCell.Interior.ColorIndex = 0
Else
activcell.Interior.ColorIndex = 18
End If

It works to toggle between colors
but only work on one cell at a time
how to make it work on all cells that i randomly select?

a big bonus would also to be able to limit this command to only work in a specific range.
something like "if selection is not in range then do nothing"

 

[My Aswer Is This]

Try this:

Sub Color_Me()
If Selection.Interior.ColorIndex = 18 Then
Selection.Interior.ColorIndex = 0
Else
Selection.Interior.ColorIndex = 18
End If
End Sub

 

[behedwin]

Try this:

Sub Color_Me()
If Selection.Interior.ColorIndex = 18 Then
Selection.Interior.ColorIndex = 0
Else
Selection.Interior.ColorIndex = 18
End If
End Sub

So simple, that worked great.

So can i fix so that the user can not change color of cells outside a specific range.

 

[My Aswer Is This]

That may not be something I can give you a answer to. Maybe someone else here at Mr. Excel will have an answer. We would need to know outside what range. And change the colors from what color. Do you mean any time you run the previous script you want it to remove colors from all cells if they are not color 18

 

[behedwin]

That may not be something I can give you a answer to. Maybe someone else here at Mr. Excel will have an answer. We would need to know outside what range. And change the colors from what color. Do you mean any time you run the previous script you want it to remove colors from all cells if they are not color 18

This code works as i intended.

Sub Color_Me()
If Selection.Interior.ColorIndex = 18 Then
Selection.Interior.ColorIndex = 0
Else
Selection.Interior.ColorIndex = 18
End If
End Sub

When above code is triggered it change the color to either 18 or 0 base on the selection that the user have made.
this works great.

This now works an the whole sheet.

if it is possible
i would like it to only work in a set range.
for example: it only changes the color to either 0 or 18 within A1:Q10
Anything outside of this range it does not change the color to anything, it does not do anything.

I trigger above code with a button placed in the sheet.
so the suer can activate the code by first making a selection of cells, then press the button.

 

[My Aswer Is This]

I do not know how to do that.

This code works as i intended.

Sub Color_Me()
If Selection.Interior.ColorIndex = 18 Then
Selection.Interior.ColorIndex = 0
Else
Selection.Interior.ColorIndex = 18
End If
End Sub

When above code is triggered it change the color to either 18 or 0 base on the selection that the user have made.
this works great.

This now works an the whole sheet.

if it is possible
i would like it to only work in a set range.
for example: it only changes the color to either 0 or 18 within A1:Q10
Anything outside of this range it does not change the color to anything, it does not do anything.

I trigger above code with a button placed in the sheet.
so the suer can activate the code by first making a selection of cells, then press the button.

 

[behedwin]

I do not know how to do that.

Thank you for the help you gave. Ill look around and hopefully figure out a way to solve this.

 

[My Aswer Is This]

I will continue to monitor this thread. If you get a answer please post it here so I can learn more also. Take care.

Thank you for the help you gave. Ill look around and hopefully figure out a way to solve this.

 

[tonyyy]

Hey behedwin and MAIT,

You might consider the following...

Sub Color_Me()
Dim rng As Range
Set rng = Intersect(Selection, Range("A1:Q10"))

If Not rng Is Nothing Then
    If rng.Interior.ColorIndex = 18 Then
        rng.Interior.ColorIndex = 0
    Else
        rng.Interior.ColorIndex = 18
    End If
End If
End Sub

Cheers,

tonyyy

 

[My Aswer Is This]

Thanks Tony for that help. I'm happy we all help out each other here.

Hey behedwin and MAIT,

You might consider the following...

Sub Color_Me()
Dim rng As Range
Set rng = Intersect(Selection, Range("A1:Q10"))

If Not rng Is Nothing Then
    If rng.Interior.ColorIndex = 18 Then
        rng.Interior.ColorIndex = 0
    Else
        rng.Interior.ColorIndex = 18
    End If
End If
End Sub

Cheers,

tonyyy