Help with colour changer

  • Thread starter Thread starter Ben Allen
  • Start date Start date
B

Ben Allen

My code show the colour picker after seecting all the cells and all the
sheets. I then want it do deselect the entire sheets after the colour picker
has been closed, any ideas? my code at the moment works before the colour
picker has been closed and so only those cells are coloured. Thanks guys.

'Change Background Colour
Private Sub Background_Click()
'Select All Sheets
Sheets.Select
'Select All Cells
Cells.Select
'Open Palette
Application.CommandBars("Fill Color").Visible = True
'Deselect Cells
Sheets("Bookings").Select
Range("B4").Select
Sheets("Income").Select
Range("B4").Select
Sheets("Costs").Select
Range("B4").Select
Sheets("PriceListsandDestinations").Select
Range("B4").Select
'Close
Unload Me
End Sub

--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"
 
Ben,

You have lost me. What is happening that you don't want, or not happening
that you do want?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob said:
Ben,

You have lost me. What is happening that you don't want, or not
happening that you do want?
Click to expand...
Basically i want this code to change the background colour of all sheets.
It selects all sheets and then loads the fill colour dialog. The once it is
filled i want it to slect just b4 of each sheet (where data starts being
entered. The only problem is this code runs before the fill dialog box has
closed. (eg it will only fill cells b4)
Any ideas?
Thanks
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"
 
Ben,

I can't see this approach working as all you do is display a toolbar, not
giving an interrupt to select from. Here is an alternative approach using my
ReturnColorindex function that does work by putting up the colour picker
dialog instead, which waits until selected.

Private Sub Background_Click()
'Select All Sheets
Sheets.Select
Cells.Select
Selection.Interior.ColorIndex = ReturnColorindex
'Deselect Cells
Sheets("Bookings").Select
Range("B4").Select
Sheets("Income").Select
Range("B4").Select
Sheets("Costs").Select
Range("B4").Select
Sheets("PriceListsandDestinations").Select
Range("B4").Select
'Close
Unload Me
End Sub

Function ReturnColorindex() As Long
Dim rngCurr As Range
Set rngCurr = Selection
Application.ScreenUpdating = False
Range("IV1").Select
Application.Dialogs(xlDialogPatterns).Show
ReturnColorindex = ActiveCell.Interior.ColorIndex
ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
rngCurr.Select
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Perfect, Thanks Bob.
Any ideas on how to do this with fonts?

--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"
 
Hi Ben,

A similar approach

Private Sub Background_Click()
MsgBox ReturnFont
End Sub

Function ReturnFont() As String
Dim rngCurr As Range
Set rngCurr = Selection
Application.ScreenUpdating = False
Range("IV1").Select
Application.Dialogs(xlDialogFont).Show
ReturnFont = ActiveCell.Font.Name
rngCurr.Select
End Function



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top