Is it possible to code a button to open a menu to select certain cells to highlight a certain color

[sloany101]

Hi , im not even sure this is doable (probably but idk) , basically what i want to know is if there is a way to code a button or something that opens a menu and this menu will do 2 things , #1 - provide a list of cells to select that will show in the list as 1-62 , you can select 1-5 and then 7 and 22 (for example) and will select them , #2 - the next step once you have selected the cells will be an action which will be to color the cell a specific color

a little insight to what i am trying to achieve - I am attempting to create a one stop macro to highlight certain cells a certain color , i have a POB that i use colors to determine whether to ( clean a bunk, a person has been moved from a different bunk, a person is a new arrival, a person is working nights, a person is part of the core crew on board, a bunk is out of service) i use a vba formula to count these cells based on the color within them , and the cells i use to determine these varying things are different so i cant just do a list of cells to select and highlight what ever color each color is assigned to different cells .

perhaps there is a way to create something in vba that opens a menu where you select an action first (clean bunks , personnel moved, Personnel on nights ETC) and once the action you want to perform is selected will then pull up a list the cells (labeled 1-62) associated with that action and once all bunks are selected and click ok or what ever is coded and will color those cells the color needed.

not sure if this is obtainable but thought id give it a shot.

 

[shinigamilight]

Sub work()
        Dim rng, rng2 As Range
      
        Dim Colorselector As Variant
        On Error GoTo endit
        Set rng = Application.InputBox("Select the range of cells", , Selection.Address, , , , , 8)
      
        Colorselector = Application.InputBox("The color must be in a number", "Select the color", 65535, , , , , 1)
        If Colorselector = False Then GoTo endit
      
        'use the below function COLORFINDER to find a color of the cell and input its value when prompted
      
        For Each rng2 In rng
            rng2.Interior.Color = Colorselector
      
        Next rng2
      
  
endit:

End Sub

Function COLORFINDER(place As Range)
            COLORFINDER = place.Interior.Color 'select a colored cell it will give you a number, copy that value and paste when prompted above 
          
End Function

 

[sloany101]

Sub work()
        Dim rng, rng2 As Range
    
        Dim Colorselector As Variant
        On Error GoTo endit
        Set rng = Application.InputBox("Select the range of cells", , Selection.Address, , , , , 8)
    
        Colorselector = Application.InputBox("The color must be in a number", "Select the color", 65535, , , , , 1)
        If Colorselector = False Then GoTo endit
    
        'use the below function COLORFINDER to find a color of the cell and input its value when prompted
    
        For Each rng2 In rng
            rng2.Interior.Color = Colorselector
    
        Next rng2
    
 
endit:

End Sub

Function COLORFINDER(place As Range)
            COLORFINDER = place.Interior.Color 'select a colored cell it will give you a number, copy that value and paste when prompted above
        
End Function

the code to select the cells to highlight and what color to highlight them works , though i am having some trouble with the color finder , not sure where or what to put it at/in , little help .

also this is a neat code and i like it , though i am looking for something a bit more user friendly as the person opposite of me is not the best at excel and does not wish to be any better than he is, i was thinking something that pops a new menu with check boxes numbered 1-62 select the ones you want and it will multiselect the cells that are assigned to that check box and once you click ok it will color the cell the color assigned to the macro, something along those lines like a userform perhaps ??

 

[shinigamilight]

the code to select the cells to highlight and what color to highlight them works , though i am having some trouble with the color finder , not sure where or what to put it at/in , little help .

also this is a neat code and i like it , though i am looking for something a bit more user friendly as the person opposite of me is not the best at excel and does not wish to be any better than he is, i was thinking something that pops a new menu with check boxes numbered 1-62 select the ones you want and it will multiselect the cells that are assigned to that check box and once you click ok it will color the cell the color assigned to the macro, something along those lines like a userform perhaps ??

The colorfinder is a user defined function, just like you type a formula in excel and point it to the cell whose color you want to find and it will give you a number. That number is the color of the cell copy that number and enter when prompted by the macro. I wrote that function so you can use your custom colors. I will make it more customizable tomorrow, and see what I can do with it.

 

[sloany101]

The colorfinder is a user defined function, just like you type a formula in excel and point it to the cell whose color you want to find and it will give you a number. That number is the color of the cell copy that number and enter when prompted by the macro. I wrote that function so you can use your custom colors. I will make it more customizable tomorrow, and see what I can do with it.


View attachment 82554

AHHHHH , so its basically a custom formula that you type into essentially a reference legend on the sheet so when it prompts you to input a number for the color you will know what to input , gotcha , it goes into a module for the workbook , THANKS!!

 

[shinigamilight]

the code to select the cells to highlight and what color to highlight them works , though i am having some trouble with the color finder , not sure where or what to put it at/in , little help .

also this is a neat code and i like it , though i am looking for something a bit more user friendly as the person opposite of me is not the best at excel and does not wish to be any better than he is, i was thinking something that pops a new menu with check boxes numbered 1-62 select the ones you want and it will multiselect the cells that are assigned to that check box and once you click ok it will color the cell the color assigned to the macro, something along those lines like a userform perhaps ??

I'd advise to avoid the checkbox approach because it will make it really long and volatile, I'd have to create 62 checkboxes manually and assign them each to a cell. There's an alternative, you can ctrl and click all the cells you want to highlight before you execute the code and it will automatically select all those cells and color it.

I attached an updated code below.

Sub work()
        Dim rng As Range
        Dim Colorselector As Variant
        
        On Error GoTo endit
        
        Set rng = Application.InputBox(Prompt:="Select the range of cells", Default:=Selection.Address, Type:=8)
        Colorselector = Application.InputBox(Prompt:="The color must be in a number", Title:="Select the color", _
                                Default:=65535, Type:=1)
        
            If Colorselector = False Then GoTo endit
      
      'use the below function COLORFINDER to find a color of the cell and input its value when prompted
            
            rng.Interior.Color = Colorselector
      
            
  
endit:

End Sub

Function COLORFINDER(place As Range)
            
            COLORFINDER = place.Interior.Color 'select a colored cell it will give you a number, copy that value and paste when prompted above
          
End Function

 

[sloany101]

I'd advise to avoid the checkbox approach because it will make it really long and volatile, I'd have to create 62 checkboxes manually and assign them each to a cell. There's an alternative, you can ctrl and click all the cells you want to highlight before you execute the code and it will automatically select all those cells and color it.

I attached an updated code below.

Sub work()
        Dim rng As Range
        Dim Colorselector As Variant
       
        On Error GoTo endit
       
        Set rng = Application.InputBox(Prompt:="Select the range of cells", Default:=Selection.Address, Type:=8)
        Colorselector = Application.InputBox(Prompt:="The color must be in a number", Title:="Select the color", _
                                Default:=65535, Type:=1)
       
            If Colorselector = False Then GoTo endit
     
      'use the below function COLORFINDER to find a color of the cell and input its value when prompted
           
            rng.Interior.Color = Colorselector
     
           
 
endit:

End Sub

Function COLORFINDER(place As Range)
           
            COLORFINDER = place.Interior.Color 'select a colored cell it will give you a number, copy that value and paste when prompted above
         
End Function

Thank you very much , i got your code working last night it does work very well , i did make a separate one with the individual check boxes that i got working , and i did have to code each individual but i put it in a userform in a multipage box and attached it to an activex button that pulls up a menu and will highlight the cell a designated color when checked and will highlight it back white when unchecked and it works nicely as well , though it is much more time consuming but i believe it is more user friendly for the person that works opposite of me due to him not being very excel savy , i certainly do appreciate the time you took for this i am certainly going to save this and apply it else where .