Using Conditional Formating /

[carl]

I used the conditional formating on this table:

BOX050 BOX060 BOX355
QQQQ 1 0 1
AAPL 1 0 1
SPY 0 0 1
INTC 1 0 1
MSFT 1 0 1
CSCO 1 0 1
AMD 1 0 1
AMAT 1 0


Where there is a 1, the cell gets shaded.

I would like to copy the table to another worksheet, remove the data (1's
and 0's) but preserve the cell shading.

Thank you in advance.

 

[Dav]

If your conditional formating is based on value=1 you can just copy the
table
the data can be deleted but the formatting will still work

Just do it!

Regards

Dav

 

[Bob Phillips]

If the CF is dependent on the data, delete the data and the CF goes. Why
don't you just change the font colour to the same as the cell colour on a
match, it will then hide it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[carl]

Thanks Bob.

I am trying to preserve the color. Then I am going to place another formula
in each cell which will not return a 1 or 0.

I am trying to visualize the cells that were equal to 1 (with the first
formula) but are now a different value with the new formula in each cell.

 

[Bob Phillips]

Carl,

here is some code that will pick up the CF colour, and add that as cell
colour, so run this after selecting the cells, then you can delete the CF.

'---------------------------------------------------------------------
Public Sub PickupCFColor()
'---------------------------------------------------------------------
Dim cell As Range
Dim ci

For Each cell In Selection
ci = CFColorindex(cell)
If ci <> False Then
cell.Interior.ColorIndex = ci
End If
Next cell

End Sub


'---------------------------------------------------------------------
Public Function CFColorindex(rng As Range)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

Set rng = rng(1, 1)
If rng.FormatConditions.Count > 0 Then
For Each oFC In rng.FormatConditions
If oFC.Type = xlCellValue Then
Select Case oFC.Operator
Case xlEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlNotEqual
CFColorindex = rng.Value <> oFC.Formula1
Case xlGreater
CFColorindex = rng.Value > oFC.Formula1
Case xlGreaterEqual
CFColorindex = rng.Value >= oFC.Formula1
Case xlLess
CFColorindex = rng.Value < oFC.Formula1
Case xlLessEqual
CFColorindex = rng.Value <= oFC.Formula1
Case xlBetween
CFColorindex = (rng.Value >= oFC.Formula1 And _
rng.Value <= oFC.Formula2)
Case xlNotBetween
CFColorindex = (rng.Value < oFC.Formula1 Or _
rng.Value > oFC.Formula2)
End Select
Else
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = rng.Row
iColumn = rng.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
CFColorindex = rng.Parent.Evaluate(sF1)
End If

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

End Function

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)