change all of the same fill colors in a worksheet
I like to try various fill colors in presentations. Can I change all of the
same fill colors in aworksheet at the same time? |
change all of the same fill colors in a worksheet
Hi Bob,
It's not very elegant but you can do a global change by going to ToolsOptionsColor 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 |
change all of the same fill colors in a worksheet
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 "MartinW" wrote in message ... Hi Bob, It's not very elegant but you can do a global change by going to ToolsOptionsColor 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 |
All times are GMT +1. The time now is 05:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com