Color fill button

[Gruntie]

I work with Excel all day at my job and must frequently change the fill color of the cells. Is there a way to have more than one colorfill button available so that I don't have to continually go into the chart and select the color I need? It may only take a second or two to select the color but I literally have to change colors hundreds of times per day and if I had a solution it would probably save 20-25 minutes or more over the course of my workday.


Thanks in advance,


Gruntie

 

[Hui]

Gruntie

Did you read this post ?


http://chandoo.org/forums/topic/how-to-change-default-fill-colour-in-excel-2007

 

[Gruntie]

Yes, I have read that post/thread. That post seems to be about changing colors. At my job I literally have to go into the palette and select the color I want hundreds of times in an 8 hour shift. What I want is to have 2 or three color fill buttons displayed with the colors I use the most.


The cell default color is usually pink. As I work I change the cells to green. Then later I change the cells to blue, red or back to pink. This takes several click each time I change the color. I need to open the palette each time I change a cell color. If I had several color fill buttons along the top of the page I would not have to open the palette and select the color I need. With several buttons I could juct click once on the button with the color I need for the cell.


Thanks!


Gruntie

 

[Hui]

Gruntie

You can assign a simple macro to either a ToolBar Button, Macro Button or Shape on your spreadsheet and then push each button to assign a color to the current Cell or Selected Range.


You can make the top row of your worksheet stationary using viewing panes or freeze panes and put the Macro Button there


Use the following code as a guide, which must be copied into a Code Module in VBA

Copy the code and change the Sub ColorCell_Red to ColorCell_Purple etc


This will work on Single Cells or a Range of selected Cells

[pre]

Sub ColorCell_Red(Target As Range)

With Selection.Interior 'Standard Colors
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With

End Sub

To know what color to put in each macro

record a Macro with the Macro recorder and just apply a color to a cell and stop recording.

Look at the code it will have a section like the 3 below defining the cells color.

Copy the entire section from With Selection to End With

[b]Standard Colors

[pre][code]With Selection.Interior 'Standard Colors
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
[b]Custom Colors[/b]

With Selection.Interior 'Custom Color
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13428300
.TintAndShade = 0
.PatternTintAndShade = 0
End With

[/pre]
Theme Colors[/b]

With Selection.Interior 'Theme Color
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With[/code][/pre]

 

[jdmaybee]

This may be a bit pedestrain, but why not set up your top line with a cell for each color you want to use and then use the paintbrush to paint the cell you want?