Conditional NumberFormat
This is a second conditional formatting example. In this case I
wanted a certain designated cells to exhibit a behaviour of taking on
a fixed color when they were blank and to turn clear if they had
something in them. Putting the following in a Module did the trick:
Sub setSelectedFormat()
setFormat
End Sub
Sub setFormat(Optional rng As Range)
If rng Is Nothing _
Then Set rng = Excel.Selection
With rng.FormatConditions
.Delete
.Add Type:=xlExpression, _
Formula1:="=ISBLANK(RC)"
.Item(1).Interior.ColorIndex = 27
.Add Type:=xlExpression, _
Formula1:="=NOT(ISBLANK(RC))"
.Item(2).Interior.ColorIndex = 0
End With
End Sub
I assigned the upper function a control key in Excel via Tools \ Macro
\ Macros (Alt+F8) \ Options. Evidently, Excel is unhappy running a
sub with an optional argument which is why there are two functions.
Also, note that copying the format from another cell to one of these
conditional ones will remove the conditional formatting.
Csaba Gabor from Vienna
|