change all of the same fill colors in a worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bobt - 25 Feb 2007 11:16 GMT

I like to try various fill colors in presentations. Can I change all of the
same fill colors in aworksheet at the same time?

Reply to this Message

MartinW - 25 Feb 2007 12:22 GMT

Hi Bob,

It's not very elegant but you can do a global change by
going to Tools>Options>Color Tab then click on a color
square and click modify. This will change any cell using that
tab to whatever you like.

There is probably a way to pre-set this sort of funcionality
with VBA but it would need to be something that is set up
for your individual needs rather than a cure-all that will work
in any situation.

I know virtually nothing about VBA but it might be worth
posting a question in the programming newsgroup.

HTH
Martin

Reply to this Message

Bob Phillips - 25 Feb 2007 13:44 GMT

Here is code to follow that route

Sub Macro1()
Dim iOldCI As Long
Dim iNewCI As Long
With ActiveCell.Interior
If .ColorIndex <> xlColorIndexNone Then
iOldCI = .Color
ActiveWorkbook.Colors(.ColorIndex) = GetColor()
End If
End With
End Sub

'-------------------------------------------------------------------------
Function GetColor(Optional Text As Boolean = False) As Long
'-------------------------------------------------------------------------
Dim rngCurr As Range
Dim prev As Worksheet
Dim sh As Worksheet
Set prev = ActiveWorkbook.ActiveSheet
Set rngCurr = Selection
Set sh = ActiveWorkbook.Worksheets.Add
Application.ScreenUpdating = False
With sh
.Range("IV1").Select
Application.Dialogs(xlDialogPatterns).Show
GetColor = ActiveCell.Interior.Color
If GetColor = xlColorIndexAutomatic And Not Text Then
GetColor = xlColorIndexNone
End If
ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
prev.Activate
rngCurr.Select
Set rngCurr = ActiveSheet.UsedRange
End With
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Function

> Hi Bob,
>
[quoted text clipped - 13 lines]
> HTH
> Martin

Reply to this Message