change color on slection with VBA

behedwin

Active Member
Joined
Dec 10, 2014
Messages
393
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

Code:
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"
 
Last edited:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,653
Office Version
  1. 2013
Platform
  1. Windows
Try this:

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

behedwin

Active Member
Joined
Dec 10, 2014
Messages
393
Try this:

Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,653
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Dec 10, 2014
Messages
393

ADVERTISEMENT

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.
Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,653
Office Version
  1. 2013
Platform
  1. Windows
I do not know how to do that.
This code works as i intended.
Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,653
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
Hey behedwin and MAIT,

You might consider the following...

Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,653
Office Version
  1. 2013
Platform
  1. Windows
Thanks Tony for that help. I'm happy we all help out each other here.
Hey behedwin and MAIT,

You might consider the following...

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,355
Members
418,498
Latest member
nattynat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top