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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

[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)

"carl" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> >
> >
> >