PDA

View Full Version : conditional format



lior03
02-19-2006, 03:42 AM
hello
i am trying to shade every second row in a selection with color , both font and interior.how do i use application.dialogs to allow the user to pick colors,for the font and the interior.
thanks

Application.ScreenUpdating = False
selection.CurrentRegion.Select
selection.FormatConditions.Delete
selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
selection.FormatConditions(1).Interior.ColorIndex = x
x = Application.Dialogs(xlDialogFormatText).Show
selection.FormatConditions(1).Font.ColorIndex = Y
Y = Application.Dialogs(xlDialogFontProperties).Show
Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub

Bob Phillips
02-19-2006, 05:37 AM
Sub lior03()
Application.ScreenUpdating = False
Dim X As Long
Dim Y As Long
With Selection.CurrentRegion
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=MOD(ROW(),2)=0"
X = GetColorindex()
.FormatConditions(1).Interior.ColorIndex = X
Y = GetColorindex(True)
.FormatConditions(1).Font.ColorIndex = Y
End With
Application.ScreenUpdating = True
End Sub


'-----------------------------------------------------------------
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