Changing cell fill color and text color using a ActiveX Button

cr130

New Member
Joined
Oct 3, 2023
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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).
 

Attachments

  • 1.png
    1.png
    5.3 KB · Views: 28

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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.

VBA Code:
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
 
Upvote 1
Solution
Does this do what you need?

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

VBA Code:
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?
 
Upvote 0
is there a way i can make it have a border around it once color changed to red?
VBA Code:
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
 
Upvote 1
you are awesome, thank you so much! still trying to learn all this code. any suggestions on where to learn more?
 
Upvote 0
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.
 
Upvote 1
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!
 
Upvote 0
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.

Excel Formula:
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
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

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
Back
Top