Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditionally setting background color of a cell
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditionally setting background color of a cell
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I change cell background color only when cursor is over it | Excel Discussion (Misc queries) | |||
Setting Precision when using &"Characters"& in a cell reference | Excel Discussion (Misc queries) | |||
Cell Background Color Change according to numerical value | Excel Discussion (Misc queries) | |||
background color of my cell does not change | Excel Discussion (Misc queries) | |||
setting cell color | Excel Worksheet Functions |