Advertisement
Advertisement
| 07.17.2008 at 02:44AM PDT, ID: 23572567 | Points: 125 |
|
[x]
Attachment Details
|
||
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83: 84: 85: 86: 87: 88: 89: 90: 91: 92: 93: 94: 95: 96: 97: 98: 99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: |
Option Explicit
Sub PasteFC()
Dim rWhole As Range
Dim rCell As Range
Dim ndx As Integer
Dim FCFont As Font
Dim FCBorder As Border
Dim FCInt As Interior
Dim x As Integer
' Dim iBorders(3) As Integer
'
' iBorders(0) = xlLeft
' iBorders(1) = xlRight
' iBorders(2) = xlTop
' iBorders(3) = xlBottom
GetRange2
Application.ScreenUpdating = False
Set rWhole = Selection
For Each rCell In rWhole
rCell.Select
ndx = ActiveCondition(rCell)
If ndx <> 0 Then
'Change the Font info
Set FCFont = rCell.FormatConditions(ndx).Font
With rCell.Font
.Bold = NewFC(.Bold, FCFont.Bold)
.Italic = NewFC(.Italic, FCFont.Italic)
.Underline = NewFC(.Underline, FCFont.Underline)
.Strikethrough = NewFC(.Strikethrough, _
FCFont.Strikethrough)
.ColorIndex = NewFC(.ColorIndex, FCFont.ColorIndex)
End With
'Change the Border Info for each of the 4 types
' For x = 0 To 3
' Set FCBorder = rCell.FormatConditions(ndx).Borders(iBorders(x))
' With rCell.Borders(iBorders(x))
' .LineStyle = NewFC(.LineStyle, FCBorder.LineStyle)
' .Weight = NewFC(.Weight, FCBorder.Weight)
' .ColorIndex = NewFC(.ColorIndex, FCBorder.ColorIndex)
' End With
' Next x
'Change the interior info
Set FCInt = rCell.FormatConditions(ndx).Interior
With rCell.Interior
.ColorIndex = NewFC(.ColorIndex, FCInt.ColorIndex)
.Pattern = NewFC(.Pattern, FCInt.Pattern)
End With
'Delete FC
rCell.FormatConditions.Delete
End If
Next
rWhole.Select
Application.ScreenUpdating = True
MsgBox ("The Formatting based on the Conditions" & vbCrLf & _
"in the range " & rWhole.Address & vbCrLf & _
"has been made standard for those cells" & vbCrLf & _
"and the Conditional Formatting has been removed")
End Sub
Function NewFC(vCurrent As Variant, vNew As Variant)
If IsNull(vNew) Then
NewFC = vCurrent
Else
NewFC = vNew
End If
End Function
Function ActiveCondition(rng As Range) As Integer
'Chip Pearson http://www.cpearson.com/excel/CFColors.htm
Dim ndx As Long
Dim FC As FormatCondition
If rng.FormatConditions.Count = 0 Then
ActiveCondition = 0
Else
For ndx = 1 To rng.FormatConditions.Count
Set FC = rng.FormatConditions(ndx)
Select Case FC.Type
Case xlCellValue
Select Case FC.Operator
Case xlBetween
If CDbl(rng.Value) >= CDbl(FC.Formula1) And _
CDbl(rng.Value) <= CDbl(FC.Formula2) Then
ActiveCondition = ndx
Exit Function
End If
Case xlGreater
If CDbl(rng.Value) > CDbl(FC.Formula1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlEqual
If CDbl(rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlGreaterEqual
If CDbl(rng.Value) >= CDbl(FC.Formula1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlLess
If CDbl(rng.Value) < CDbl(FC.Formula1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlLessEqual
If CDbl(rng.Value) <= CDbl(FC.Formula1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlNotEqual
If CDbl(rng.Value) <> CDbl(FC.Formula1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlNotBetween
If CDbl(rng.Value) <= CDbl(FC.Formula1) Or _
CDbl(rng.Value) >= CDbl(FC.Formula2) Then
ActiveCondition = ndx
Exit Function
End If
Case Else
Debug.Print "UNKNOWN OPERATOR"
End Select
Case xlExpression
If Application.Evaluate(FC.Formula1) Then
ActiveCondition = ndx
Exit Function
End If
Case Else
Debug.Print "UNKNOWN TYPE"
End Select
Next ndx
End If
ActiveCondition = 0
End Function
Private Sub GetRange2()
'gets the range you want to highlight and sets it to be called "data"
'this is essential for your conditional formatting rule
Dim rng As Range
On Error Resume Next
ActiveSheet.Select
Set rng = Application.InputBox(prompt:="Please select the cells that you want to convert the conditional formatting on", Type:=8)
If rng Is Nothing Then
MsgBox "Operation Cancelled"
Sheet1.Select
Range("A1").Select
Application.ScreenUpdating = True
End
Else
rng.Select
ActiveWorkbook.Names.Add Name:="data", RefersToR1C1:=rng
End If
End Sub
|