Changing cell fill color and text color using a ActiveX Button

[cr130]

Hello All,

I am trying to click a Active X button and have it change the color of a two cell colors (E5 and E6) and text color and then when clicked again it goes back to the previous color. If this is not possible to do with a button, can it be done with a checkbox?

Right now I have conditional formatting to make all the cells white text and white fill (so it looks empty). Then when the button or checkbox is clicked it changes the text to black and the fill color to Red (RGB 220,86,65).

 

[HighAndWilder]

Hello All,

I am trying to click a Active X button and have it change the color of a two cell colors (E5 and E6) and text color and then when clicked again it goes back to the previous color. If this is not possible to do with a button, can it be done with a checkbox?

Right now I have conditional formatting to make all the cells white text and white fill (so it looks empty). Then when the button or checkbox is clicked it changes the text to black and the fill color to Red (RGB 220,86,65).

Does this do what you need?

Just copy and paste the code in the sub into your sub.

Private Sub cmdChangeColours_Click()

   With ActiveSheet
    
      If .Range("E5").Interior.Color = vbWhite Then
        
        With .Range("E5:E6")
          .Interior.Color = RGB(220, 86, 65)
          .Font.Color = RGB(0, 0, 0)
        End With
      
      Else
      
        With .Range("E5:E6")
          .Font.Color = RGB(255, 255, 255)
          .Interior.ColorIndex = xlNone
        End With
      
      End If
  
    End With
  
End Sub

 

[cr130]

hello,

how do i tie it to the button?

 

[cr130]

wait nvm, i got it. thank you!!

 

[cr130]

Does this do what you need?

Just copy and paste the code in the sub into your sub.

Private Sub cmdChangeColours_Click()

   With ActiveSheet
   
      If .Range("E5").Interior.Color = vbWhite Then
       
        With .Range("E5:E6")
          .Interior.Color = RGB(220, 86, 65)
          .Font.Color = RGB(0, 0, 0)
        End With
     
      Else
     
        With .Range("E5:E6")
          .Font.Color = RGB(255, 255, 255)
          .Interior.ColorIndex = xlNone
        End With
     
      End If
 
    End With
 
End Sub

is there a way i can make it have a border around it once color changed to red?

 

[HighAndWilder]

is there a way i can make it have a border around it once color changed to red?

Private Sub cmdChangeColours_Click()

   With ActiveSheet
    
      If .Range("E5").Interior.Color = vbWhite Then
        
        With .Range("E5:E6")
          .Interior.Color = RGB(220, 86, 65)
          .Font.Color = RGB(0, 0, 0)
        End With
      
        Range("E5:E6").BorderAround _
          LineStyle:=xlContinuous, _
          Weight:=xlThick, _
          Color:=RGB(255, 255, 0)
      
      Else
      
        With .Range("E5:E6")
          .Font.Color = RGB(255, 255, 255)
          .Interior.ColorIndex = xlNone
          With .Borders
            .LineStyle = xlNone
          End With
        End With
      
      End If
  
    End With
  
End Sub

 

[cr130]

you are awesome, thank you so much! still trying to learn all this code. any suggestions on where to learn more?

 

[HighAndWilder]

you are awesome, thank you so much! still trying to learn all this code. any suggestions on where to learn more?

Glad that it works for you.

I'm sorry to say that you will never learn 'all this code', there is just too much and they bring out more functions with every version.

BUT, there are so many good web sites and YouTube videos.

I like the Leila Grarani videos, https://www.youtube.com/@LeilaGharani. She is very clear and precise.

Start with achieving simple things such as opening and closing / saving workbooks, adding / deleting sheets and copying and pasting ranges of data.

Use the macro recorder but be aware that you often get a lot of unnecessary code.

Learn how to format cells as well.

Simple formulas doing arithmetical calculations is a good way to start.

And of course MrExcel is always here to help. One of the keys to getting a solution on here is to explain the problem well. Those posts
that will involve a lot of questions and unraveling often don't get responses.

If you can upgrade to 365 it will help you as that version has some amazing new functions that we have been waiting for for years.

If you get really stuck and frustrated, move away and go and do something else and come back later. Some problems
are solved by our brains whilst we sleep.

 

[cr130]

Glad that it works for you.

I'm sorry to say that you will never learn 'all this code', there is just too much and they bring out more functions with every version.

BUT, there are so many good web sites and YouTube videos.

I like the Leila Grarani videos, https://www.youtube.com/@LeilaGharani. She is very clear and precise.

Start with achieving simple things such as opening and closing / saving workbooks, adding / deleting sheets and copying and pasting ranges of data.

Use the macro recorder but be aware that you often get a lot of unnecessary code.

Learn how to format cells as well.

Simple formulas doing arithmetical calculations is a good way to start.

And of course MrExcel is always here to help. One of the keys to getting a solution on here is to explain the problem well. Those posts
that will involve a lot of questions and unraveling often don't get responses.

If you can upgrade to 365 it will help you as that version has some amazing new functions that we have been waiting for for years.

If you get really stuck and frustrated, move away and go and do something else and come back later. Some problems
are solved by our brains whilst we sleep.

thanks so much!

 

[cr130]

Glad that it works for you.

I'm sorry to say that you will never learn 'all this code', there is just too much and they bring out more functions with every version.

BUT, there are so many good web sites and YouTube videos.

I like the Leila Grarani videos, https://www.youtube.com/@LeilaGharani. She is very clear and precise.

Start with achieving simple things such as opening and closing / saving workbooks, adding / deleting sheets and copying and pasting ranges of data.

Use the macro recorder but be aware that you often get a lot of unnecessary code.

Learn how to format cells as well.

Simple formulas doing arithmetical calculations is a good way to start.

And of course MrExcel is always here to help. One of the keys to getting a solution on here is to explain the problem well. Those posts
that will involve a lot of questions and unraveling often don't get responses.

If you can upgrade to 365 it will help you as that version has some amazing new functions that we have been waiting for for years.

If you get really stuck and frustrated, move away and go and do something else and come back later. Some problems
are solved by our brains whilst we sleep.

Hey, i was trying to make it so there is vertical and horizontal borders. I was searching google and found this code but havent gotten it to work properly.

Dim rngInner As Range: Set rngInner = ws.Range("E5", "E6")        

With rngInner.Borders(xlInsideHorizontal)  
.LineStyle = xlContinuous  
.Weight = xlThin
End With
With rngInner.Borders(xlInsideVertical)  
.LineStyle = xlContinuous  
.Weight = xlThin
End With