Excel Buttons 1

[amondor10]

I am trying to create a button in excel that allows for an if elseif else statement. ie:if cell(b2:c2) ColorIndex=xlNone then ColorIndex=15 etc.
basically I have three different colors per sheet and 10 sheets. Sheet one has pink grey and none. I have been fighting with this for a while now, I hope there is someone out there that can help me.

Thank you
Angela

 

[xlbo]

what is it that you are actually trying to accomplish ??
Change the colour of the button or the colur of some cells ??

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[amondor10]

I am trying to get the cells to to change colour based on the colour that they are now. For example, if they are blank change to pink, if they are pink goto grey if they are grey goto blank.

 

[xlbo]

oh - ok - pretty easy then

Use a select case

For each rng in selection
Select case rng.interior.colorindex
case xlnone
newCol = 3
case 3
newCol = 2
case 2
newCol = 5
etc etc
case else
newCol = xlnone
end select
rng.interior.colorindex = newCol
next

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[amondor10]

Sub CommandButton1_Click()
Select Case rng.Interior.ColorIndex
Case xlNone
newCol = 3
Case 3
newCol = 2
Case 2
newCol = 5
Case Else
newCol = xlNone
End Select
rng.Interior.ColorIndex = newCol
End Sub

this is what I have... where do I select the cells (b1:c1)??

Thank you
Angela

 

[xlbo]

Sub CommandButton1_Click()
dim rng as range
set rng = range("B1:C1&quot
Select Case rng.Interior.ColorIndex
Case xlNone
newCol = 3
Case 3
newCol = 2
Case 2
newCol = 5
Case Else
newCol = xlNone
End Select
rng.Interior.ColorIndex = newCol
End Sub

You will have to set the numbers for the interior.colorindex dependant on your needs - I just chose arbitrary ones


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244