Copy the conditional format result to another worksheet in excel

Guys

I want to be able to copy the returned results of a conditional formula that changes colour to another location but without the conditional formatting

ie if the results of column Trans(Z4:Z1000) have conditional formatting altering each cell based upon a rule to different colours I want the colours and values in those cells copied to Sheet8(D2:D998) and have none of the conditional formatting.

The reason is that if the parameters that the conditional formatting uses changed then the colours would alter and I need to keep history.

Any Ideas it seems simple and I'm sure I'm just missing something here????
DarrenJacksonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dan CraciunIT ConsultantCommented:
Paste->Values & Source Formatting (E)
It's under Paste:
MS Excel paste options
HTH,
Dan
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

Dan: When you use that Copy/Paste Special option, the Conditional Formatting rules from the source worksheet/range are also copied across to the destination worksheet/range.

When the Conditional Formatting is then removed on the destination worksheet, the cell formatting applied is also removed.

I think DarrenJackson wanted the cell formatting applied on the source worksheet (courtesy of the Conditional Formatting in effect there) to be copied, but not the Conditional Formatting rule(s).

BFN,

fp.
0
DarrenJacksonAuthor Commented:
that also brings across the conditional formatting and rules that go with it

which is not what I want
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

DarrenJacksonAuthor Commented:
sorry didn't refresh but fanpages is correct
0
[ fanpages ]IT Services ConsultantCommented:
:)

This could be achieved programmatically (using Visual Basic for Applications code) by simply interrogating the formatting applied to either the source or the destination cells (one-by-one) & replicating this formatting to the destination cells following the copy, then removing the Conditional Formatting from the destination range.

I am aware of an existing routine, written by Rick Rothstein, that can be used to retrieve the colo(u)r(s) set by Conditional Formatting:

' Arguments
' ----------------
' Cell - Required Range, not a String value, for a **single** cell
'
' CellInterior - Optional Boolean (Default = True)
'                True makes function return cell's Interior Color or ColorIndex based on
'                the ReturnColorIndex argument False makes function return Font's Color or
'                ColorIndex based on the ReturnColorIndex argument
'
' ReturnColorIndex - Optional Boolean (Default = True)
'                    True makes function return the ColorIndex for the cell property determined
'                    by the CellInterior argument False make function return the Color for the
'                    cell property determined by the CellInterior argument
'
Function DisplayedColor(Cell As Range, Optional CellInterior As Boolean = True, _
                        Optional ReturnColorIndex As Long = True) As Long
  Dim X As Long, Test As Boolean, CurrentCell As String
  If Cell.Count > 1 Then Err.Raise vbObjectError - 999, , "Only single cell references allowed for 1st argument."
  CurrentCell = ActiveCell.Address
  For X = 1 To Cell.FormatConditions.Count
    With Cell.FormatConditions(X)
      If .Type = xlCellValue Then
        Select Case .Operator
          Case xlBetween:      Test = Cell.Value >= Evaluate(.Formula1) And Cell.Value <= Evaluate(.Formula2)
          Case xlNotBetween:   Test = Cell.Value <= Evaluate(.Formula1) Or Cell.Value >= Evaluate(.Formula2)
          Case xlEqual:        Test = Evaluate(.Formula1) = Cell.Value
          Case xlNotEqual:     Test = Evaluate(.Formula1) <> Cell.Value
          Case xlGreater:      Test = Cell.Value > Evaluate(.Formula1)
          Case xlLess:         Test = Cell.Value < Evaluate(.Formula1)
          Case xlGreaterEqual: Test = Cell.Value >= Evaluate(.Formula1)
          Case xlLessEqual:    Test = Cell.Value <= Evaluate(.Formula1)
        End Select
      ElseIf .Type = xlExpression Then
        Application.ScreenUpdating = False
        Cell.Select
        Test = Evaluate(.Formula1)
        Range(CurrentCell).Select
        Application.ScreenUpdating = True
      End If
      If Test Then
        If CellInterior Then
          DisplayedColor = IIf(ReturnColorIndex, .Interior.ColorIndex, .Interior.Color)
        Else
          DisplayedColor = IIf(ReturnColorIndex, .Font.ColorIndex, .Font.Color)
        End If
        Exit Function
      End If
    End With
  Next
  If CellInterior Then
    DisplayedColor = IIf(ReturnColorIndex, Cell.Interior.ColorIndex, Cell.Interior.Color)
  Else
    DisplayedColor = IIf(ReturnColorIndex, Cell.Font.ColorIndex, Cell.Font.Color)
  End If
End Function

Open in new window



Pearson Software Consulting Services also has some pre-written routines that we may be able to utilise:

[ http://www.cpearson.com/Excel/CFColors.htm ]


You're right though, Darren', there should be an easier way!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
Darren': Shall we wait to see if anybody else has a suggestion using in-built features of the Microsoft Excel product (before looking at a code-based solution)?
0
DarrenJacksonAuthor Commented:
yes give it time I will update later today if I have no responses

Thanks
0
Dan CraciunIT ConsultantCommented:
Apparently there is a easier solutions: paste to a new sheet and save the sheet as a .mht file. That saves the formatting but removed the conditional formatting.

After that copy back the cells.

HTH,
Dan
0
[ fanpages ]IT Services ConsultantCommented:
Yes, there is that, I suppose!

Well done Dan :)

[ http://office.microsoft.com/en-gb/excel-help/excel-formatting-and-features-that-are-not-transferred-to-other-file-formats-HP010014105.aspx ]

(MS-Excel 2007)
---
Web Page and Single File Web Page
 
These Web Page file formats (.htm, .html), Single File Web Page file formats (.mht, .mhtml) can be used for exporting Excel data. In Office Excel 2007, worksheet features (such as formulas, charts, PivotTables, and Visual Basic for Application (VBA) projects) are no longer supported in these file formats, and they will be lost when you open a file in this file format again in Excel.
---


[ http://office.microsoft.com/en-gb/excel-help/file-formats-that-are-supported-in-excel-HP010352464.aspx ]

(MS-Excel 2010)
---
Web Page and Single File Web Page

These Web Page file formats (.htm, .html), Single File Web Page file formats (.mht, .mhtml) can be used for exporting Excel data. In Excel 2010, worksheet features (such as formulas, charts, PivotTables, and Visual Basic for Application (VBA) projects) are no longer supported in these file formats, and they will be lost when you open a file in this file format again in Excel.
---
0
DarrenJacksonAuthor Commented:
I managed to find  solution by having the formatting removed by using this helpful post

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27472229.html
0
DarrenJacksonAuthor Commented:
Thank you all for helping me out

Cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.