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.

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=518746

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" wrote in message
...
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.

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" wrote:

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" wrote in message
...
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.

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)

"carl" wrote in message
...
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" wrote:

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" wrote in message
...
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.