software

I used the conditional formating on this table:

BOX050BOX060BOX355
QQQQ101
AAPL101
SPY001
INTC101
MSFT101
CSCO101
AMD101
AMAT10Where 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: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=518746If 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)

quot;carlquot; gt; wrote in message
...
gt; I used the conditional formating on this table:
gt;
gt; BOX050 BOX060 BOX355
gt; QQQQ 1 0 1
gt; AAPL 1 0 1
gt; SPY 0 0 1
gt; INTC 1 0 1
gt; MSFT 1 0 1
gt; CSCO 1 0 1
gt; AMD 1 0 1
gt; AMAT 1 0
gt;
gt;
gt; Where there is a 1, the cell gets shaded.
gt;
gt; I would like to copy the table to another worksheet, remove the data (1's
gt; and 0's) but preserve the cell shading.
gt;
gt; Thank you in advance.
gt;
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.

quot;Bob Phillipsquot; wrote:

gt; If the CF is dependent on the data, delete the data and the CF goes. Why
gt; don't you just change the font colour to the same as the cell colour on a
gt; match, it will then hide it.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;carlquot; gt; wrote in message
gt; ...
gt; gt; I used the conditional formating on this table:
gt; gt;
gt; gt; BOX050 BOX060 BOX355
gt; gt; QQQQ 1 0 1
gt; gt; AAPL 1 0 1
gt; gt; SPY 0 0 1
gt; gt; INTC 1 0 1
gt; gt; MSFT 1 0 1
gt; gt; CSCO 1 0 1
gt; gt; AMD 1 0 1
gt; gt; AMAT 1 0
gt; gt;
gt; gt;
gt; gt; Where there is a 1, the cell gets shaded.
gt; gt;
gt; gt; I would like to copy the table to another worksheet, remove the data (1's
gt; gt; and 0's) but preserve the cell shading.
gt; gt;
gt; gt; Thank you in advance.
gt; gt;
gt;
gt;
gt;

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 lt;gt; 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 gt; 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 lt;gt; oFC.Formula1
Case xlGreater
CFColorindex = rng.Value gt; oFC.Formula1
Case xlGreaterEqual
CFColorindex = rng.Value gt;= oFC.Formula1
Case xlLess
CFColorindex = rng.Value lt; oFC.Formula1
Case xlLessEqual
CFColorindex = rng.Value lt;= oFC.Formula1
Case xlBetween
CFColorindex = (rng.Value gt;= oFC.Formula1 And _
rng.Value lt;= oFC.Formula2)
Case xlNotBetween
CFColorindex = (rng.Value lt; oFC.Formula1 Or _
rng.Value gt; 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, quot;ROW()quot;, iRow)
sF1 = .Substitute(sF1, quot;COLUMN()quot;, 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 gt; 0

End Function

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;carlquot; gt; wrote in message
...
gt; Thanks Bob.
gt;
gt; I am trying to preserve the color. Then I am going to place another
formula
gt; in each cell which will not return a 1 or 0.
gt;
gt; I am trying to visualize the cells that were equal to 1 (with the first
gt; formula) but are now a different value with the new formula in each cell.
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; If the CF is dependent on the data, delete the data and the CF goes. Why
gt; gt; don't you just change the font colour to the same as the cell colour on
a
gt; gt; match, it will then hide it.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;carlquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I used the conditional formating on this table:
gt; gt; gt;
gt; gt; gt; BOX050 BOX060 BOX355
gt; gt; gt; QQQQ 1 0 1
gt; gt; gt; AAPL 1 0 1
gt; gt; gt; SPY 0 0 1
gt; gt; gt; INTC 1 0 1
gt; gt; gt; MSFT 1 0 1
gt; gt; gt; CSCO 1 0 1
gt; gt; gt; AMD 1 0 1
gt; gt; gt; AMAT 1 0
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Where there is a 1, the cell gets shaded.
gt; gt; gt;
gt; gt; gt; I would like to copy the table to another worksheet, remove the data
(1's
gt; gt; gt; and 0's) but preserve the cell shading.
gt; gt; gt;
gt; gt; gt; Thank you in advance.
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;