Hi,

I made a calendar using excel. In each cell there is hard coded
conditional formatting to test if the day is a Saturday or a Sunday. If
this is true then the background color is set to red i.e. I have hard
coded the color to red by pressing on the "Format..." button in the
conditional formatting dialog and setting the the background color to
red. This is done for each cell.

This isn't very flexible at all though since I might want to change the
color from red to something else. So my question is, how can I set the
color of a cell that meets my "holiday condition" in a more flexible
way, maybe using VB?

Thanks for any tips.

Here is some code. Put it in a standard code module.

Create a button and assign ChangeColour to it.

Select the range to be re-formatted and cklick the button.

Option Explicit

Sub ChangeColour()
Dim iColor As Long

iColor = GetColorindex
If iColor < xlColorIndexNone Then
CFColorindex iColor
End If
End Sub

'---------------------------------------------------------------------
Private Function CFColorindex(ColorIndex As Long)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim cell As Range
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

For Each cell In Selection
If cell.FormatConditions.Count 0 Then
For Each oFC In cell.FormatConditions
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = cell.Row
iColumn = cell.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , cell)
End With
CFColorindex = cell.Parent.Evaluate(sF1)

If CFColorindex Then
If Not IsNull(oFC.Interior.ColorIndex) Then
oFC.Interior.ColorIndex = ColorIndex
Exit For
End If
End If
Next oFC
End If 'cell.FormatConditions.Count 0
Next cell
End Function


'-----------------------------*------------------------------*--------------
--
Private Function GetColorindex(Optional Text As Boolean = False) As Long
'-----------------------------*------------------------------*--------------
--
Dim rngCurr As Range

Set rngCurr = Selection
Application.ScreenUpdating = False
Range("IV1").Select
Application.Dialogs(xlDialogPatterns).Show
GetColorindex = ActiveCell.Interior.ColorIndex
If GetColorindex = xlColorIndexAutomatic And Not Text Then
GetColorindex = xlColorIndexNone
End If
ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
rngCurr.Select
Set rngCurr = ActiveSheet.UsedRange
Application.ScreenUpdating = True
End Function






--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Scott Steiner" wrote in message
...
Hi,

I made a calendar using excel. In each cell there is hard coded
conditional formatting to test if the day is a Saturday or a Sunday. If
this is true then the background color is set to red i.e. I have hard
coded the color to red by pressing on the "Format..." button in the
conditional formatting dialog and setting the the background color to
red. This is done for each cell.

This isn't very flexible at all though since I might want to change the
color from red to something else. So my question is, how can I set the
color of a cell that meets my "holiday condition" in a more flexible
way, maybe using VB?

Thanks for any tips.