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"

 

[tonyyy]

You're welcome, MAIT. Always happy to help.

 

[Rick Rothstein]

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

If I am not mistaken, this macro should work the same as yours does...

[table="width: 500"]
[tr]
	[td]Sub ColorMe()
  Dim Rng As Range
  Set Rng = Intersect(Range("A1:Q10"), Selection)
  If Not Rng Is Nothing Then Rng.Interior.ColorIndex = -4124 - Rng.Interior.ColorIndex
End Sub[/td]
[/tr]
[/table]

 

[behedwin]

Thank you all, this worked great.

 

[tonyyy]

If I am not mistaken, this macro should work the same as yours does...
Code:

Sub ColorMe() Dim Rng As Range Set Rng = Intersect(Range("A1:Q10"), Selection) If Not Rng Is Nothing Then Rng.Interior.ColorIndex = -4124 - Rng.Interior.ColorIndex End Sub

<tbody>
</tbody>

Now you're just showing off, Rick!

Just kidding!

I like how your code is so compact, but without an explanation I don't fully understand it. Specifically, how did you get -4124 from ColorIndex 18?

 

[Rick Rothstein]

Now you're just showing off, Rick!

Maybe a little.

I like how your code is so compact, but without an explanation I don't fully understand it. Specifically, how did you get -4124 from ColorIndex 18?

When you want to toggle between two numbers, all you have to do is subtract the current value from the sum of what the two numbers can be in order to get the other (assuming that current value is one of the two possible numbers to begin with). For example, to toggle between the numbers 5 and 9, you take the sum, 14 and subtract the current value from it (but remember, that current value must already be either 5 or 9 for this to work, so it is assumed that was set at some point during initialization). So...

Value = 14 - Value

If Value equals 5, the subtraction assigns 9 to the Value... the next time the code is executed, it is 9 that is subtracted from 14 to yield 5, and so on back and forth between 5 and 9.

Okay, now, while most people use 0 for no ColorIndex, the "proper" value is house in the predefined constant xlColorIndexNone which evaluates to -4142, so the number I used, -4124, is the sum of -4142 and 18 (the two numbers that will be toggled between). The beauty of one of the numbers being "zero" (or its equivalent in this case) is that no initialization is required... the cell starts of with a setting of "zero" (-4142) and so first calculation flips it to 18 automatically with no initialization required. The next time the code is executed, 18 flips back to -4142 (the equivalent of 0) or, in other words, no fill.

 

[tonyyy]

Cool beans! Thanks for the thorough explanation, Rick. Makes perfect sense!

 

[My Aswer Is This]

He lost me at: "When you want to toggle" lol

Cool beans! Thanks for the thorough explanation, Rick. Makes perfect sense!