Freezing Conditional Formatting

Hello,
I am using MS-Office-2000.
I have applied formula-based conditional formatting on a range of cells.
Now, I want to remove the Conditions but wanna keep the formatting on the
data as it is. This will help me in Moving the data around the sheet without
loosing the formatting.
Is there any way of achieving this ?

Thanks,
Ashish


0
12/17/2003 6:12:51 AM
excel 39879 articles. 2 followers. Follow

10 Replies
827 Views

Similar Articles

[PageSpeed] 27

Does it help to select the range you want to copy>>Copy>>Selec
destination>>PasteSpecial>>Format

--
Message posted from http://www.ExcelForum.com

0
12/17/2003 6:41:43 AM
No, it copies the entire conditional formatting along with the conditions.
It looks for the same conditions at the new destination also.


"gocush" <gocush.ykjsy@excelforum-nospam.com> wrote in message
news:gocush.ykjsy@excelforum-nospam.com...
> Does it help to select the range you want to copy>>Copy>>Select
> destination>>PasteSpecial>>Formats
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
12/17/2003 6:49:57 AM
Ashish,
Are you totally opposed to keeping the conditional formatting?  You can make all the cell references in the conditional formatting window absolute by typing "$" to the row and column address.  This will ensure the formatting stays the same when you move the cells even with the copy method.

Otherwise, I don't think there are ways to keep the formatting when you eliminate the conditions.

Ron_D
0
anonymous (74722)
12/17/2003 8:26:05 AM
Ashish

Can't you then use Edit / Go to / Special / Conditional Formats and then
Format / Conditional Format / Delete?

Andy.

"Ashish Chamaria" <ashishchamaria@hotmail.com> wrote in message
news:uOK19nGxDHA.3196@TK2MSFTNGP11.phx.gbl...
> No, it copies the entire conditional formatting along with the conditions.
> It looks for the same conditions at the new destination also.
>
>
> "gocush" <gocush.ykjsy@excelforum-nospam.com> wrote in message
> news:gocush.ykjsy@excelforum-nospam.com...
> > Does it help to select the range you want to copy>>Copy>>Select
> > destination>>PasteSpecial>>Formats
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
> >
>
>


0
andy1646 (82)
12/17/2003 8:43:05 AM
Deleting conditions also removes the formatting that had got applied to the
cell. I want to keep the formatting which appeared as a result of those
conditions, but want to remove those conditions so that the formatting no
longer remains dynamic but gets fixed on the data in that cell.

Ashish.

"Andy B" <andy@takethisbitout.dawsons.co.uk> wrote in message
news:e5vLOmHxDHA.3468@TK2MSFTNGP11.phx.gbl...
> Ashish
>
> Can't you then use Edit / Go to / Special / Conditional Formats and then
> Format / Conditional Format / Delete?
>
> Andy.
>
> "Ashish Chamaria" <ashishchamaria@hotmail.com> wrote in message
> news:uOK19nGxDHA.3196@TK2MSFTNGP11.phx.gbl...
> > No, it copies the entire conditional formatting along with the
conditions.
> > It looks for the same conditions at the new destination also.
> >
> >
> > "gocush" <gocush.ykjsy@excelforum-nospam.com> wrote in message
> > news:gocush.ykjsy@excelforum-nospam.com...
> > > Does it help to select the range you want to copy>>Copy>>Select
> > > destination>>PasteSpecial>>Formats
> > >
> > >
> > > ---
> > > Message posted from http://www.ExcelForum.com/
> > >
> >
> >
>
>


0
12/17/2003 9:16:42 AM
Actually I had to apply some colour-codes on the data based on certain
conditions. The datasheet that I have contains around 6000 cells of data. So
I created a conditional formatting formula which I copied on all the 6000
cells to get the desired color formatting for different kinds of data. The
conditional formatting formula itself is dynamic as it is not based on any
one particular cell and thats why I cant use absolute referencing in this
formula as it later has also to be applied on all the 6000 cells.

But once the cells have been colored on the basis of conditional formatting,
I want to keep the colours and remove the conditional formatting from the
cells, because now if I MOVE just a part of that data to someplace else in
the sheet, it rechecks for those conditional formatting checks as per the
new location of the data and reformats the data.

Please help.

Thanks
Ashish

"Ron_D" <anonymous@discussions.microsoft.com> wrote in message
news:9CA02A5E-493F-496F-8ED5-3ED6911CDA0F@microsoft.com...
> Ashish,
> Are you totally opposed to keeping the conditional formatting?  You can
make all the cell references in the conditional formatting window absolute
by typing "$" to the row and column address.  This will ensure the
formatting stays the same when you move the cells even with the copy method.
>
> Otherwise, I don't think there are ways to keep the formatting when you
eliminate the conditions.
>
> Ron_D


0
12/17/2003 9:28:24 AM
This doesn't look trivial to me.

But Chip Pearson did most of the work.

I went to his site:
http://www.cpearson.com/excel/CFColors.htm

And stole his ActiveCondition function.

(I did make a change to it because of an oddity in excel:  See John Walkenbach's
site:
http://j-walk.com/ss/excel/odd/odd07.htm to see more information.)

I got help from both John and Bernie Deitrick on how to overcome this bleeping
oddity!

(Both John's and Bernie's tip seemed to work ok for me.  I included (but
commented out) John's version.  I used Bernie's (simply because it was more
simple!).  (I think I would have had to activate a different worksheet in either
case.  And if I have to activate a worksheet, I might as well just select the
cell!--it goes against a lot of things I've learned here, but you gotta do what
works.)

Chip's code is the workhorse.  It determines which condition is active.  The
code that calls it just removes the non-active format conditions and replaces
the activecondition with True.  (so it always stays active).

So no matter what's in the cell, the conditional formatting that was there will
always apply (well, until you change it.)

I've included Chip's code here only because of the slight changes I made.  

(The notes and most of the procedure came from a similar request:
http://groups.google.com/groups?threadm=3DEFDE80.280201E0%40msn.com)

And had this followup:

One thing that I didn't think of (and I hope that it doesn't affect you).

If you have cells that evaluate to errors (1/0, ref, n/a type stuff), then the
ActiveCondition function blows up.  The code uses a lot of .values.  


===============
Try this against a copy of your worksheet--just in case.

Option Explicit
Sub testme()

    Dim myRng As Range
    Dim mycell As Range
    Dim myCell_AC As Long
    Dim wks As Worksheet
    Dim i As Integer
    Dim startCell As Range
    
    Set startCell = ActiveCell
    
    For Each wks In ActiveWorkbook.Worksheets
        With wks
            Set myRng = Nothing
            On Error Resume Next
            Set myRng = .Cells.SpecialCells(xlCellTypeAllFormatConditions)
            On Error GoTo 0
            
            If myRng Is Nothing Then
                'do nothing
            Else
                application.screenupdating = false
                For Each mycell In myRng.Cells
                    If mycell.FormatConditions.Count = 0 Then
                        MsgBox "something bad happened with " & _
                            mycell.Address(external:=True)
                        'do nothing
                    Else
                        myCell_AC = ActiveCondition(mycell)
                        If myCell_AC = 0 Then
                            mycell.FormatConditions.Delete
                        Else
                            For i = mycell.FormatConditions.Count To 1 Step -1
                                If i = myCell_AC Then
                                    mycell.Interior.ColorIndex _
                                         = mycell.FormatConditions(i) _
                                                 .Interior.ColorIndex
                                End If
                                mycell.FormatConditions(i).Delete
                            Next i
                        End If
                    End If
                Next mycell
                application.screenupdating = true
            End If
        End With
    Next wks
    
    Application.Goto startCell
    
End Sub

Function ActiveCondition(Rng As Range) As Integer

Dim Ndx As Long
Dim FC As FormatCondition
Dim tmpRng As Range

Set tmpRng = Rng
Set Rng = Nothing
Set Rng = tmpRng

If Rng.FormatConditions.Count = 0 Then
    ActiveCondition = 0
Else
    For Ndx = 1 To Rng.FormatConditions.Count
    Set FC = Nothing
    Set FC = Rng.FormatConditions(Ndx)
    Set FC = Rng.FormatConditions(Ndx)
    Select Case FC.Type
       Case xlCellValue
           Select Case FC.Operator
               Case xlBetween
                   If CDbl(Rng.Value) >= CDbl(FC.Formula1) And _
                      CDbl(Rng.Value) <= CDbl(FC.Formula2) Then
                      ActiveCondition = Ndx
                      Exit Function
                   End If

               Case xlGreater
                   If CDbl(Rng.Value) > CDbl(FC.Formula1) Then
                       ActiveCondition = Ndx
                       Exit Function
                   End If

               Case xlEqual
                   If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
                      ActiveCondition = Ndx
                      Exit Function
                   End If

               Case xlGreaterEqual
                   If CDbl(Rng.Value) >= CDbl(FC.Formula1) Then
                       ActiveCondition = Ndx
                       Exit Function
                   End If

               Case xlLess
                   If CDbl(Rng.Value) < CDbl(FC.Formula1) Then
                      ActiveCondition = Ndx
                      Exit Function
                   End If

               Case xlLessEqual
                   If CDbl(Rng.Value) <= CDbl(FC.Formula1) Then
                      ActiveCondition = Ndx
                      Exit Function
                   End If

               Case xlNotEqual
                   If CDbl(Rng.Value) <> CDbl(FC.Formula1) Then
                       ActiveCondition = Ndx
                       Exit Function
                   End If

               Case xlNotBetween
                   If CDbl(Rng.Value) <= CDbl(FC.Formula1) Or _
                      CDbl(Rng.Value) >= CDbl(FC.Formula2) Then
                     ActiveCondition = Ndx
                     Exit Function
                   End If

               Case Else
                   Debug.Print "UNKNOWN OPERATOR"
         End Select


      Case xlExpression
'        John Walkenbach 's excel oddity page
'        http://j-walk.com/ss/excel/odd/odd07.htm
'        describes the problem
'
'        Bernie Deitrick's tip about selecting the cell first to make formula1
'        work correctly works fine, too.
'
'        from John's site:
'          Dim F1 As String
'          Dim F2 As String'
'          Rng.Parent.Activate 'make F2 formula work with activecell.
'          F1 = Rng.FormatConditions(1).Formula1
'          F2 = Application.ConvertFormula(F1, xlA1, xlR1C1, , ActiveCell)
'          F1 = Application.ConvertFormula(F2, xlR1C1, xlA1, , Rng)

'        From Bernie's tip
         Application.Goto Rng
         
          If Application.Evaluate(FC.Formula1) Then
             ActiveCondition = Ndx
             Exit Function
          End If

     Case Else
           Debug.Print "UNKNOWN TYPE"
     End Select

  Next Ndx

End If

ActiveCondition = 0

End Function



Ashish Chamaria wrote:
> 
> Actually I had to apply some colour-codes on the data based on certain
> conditions. The datasheet that I have contains around 6000 cells of data. So
> I created a conditional formatting formula which I copied on all the 6000
> cells to get the desired color formatting for different kinds of data. The
> conditional formatting formula itself is dynamic as it is not based on any
> one particular cell and thats why I cant use absolute referencing in this
> formula as it later has also to be applied on all the 6000 cells.
> 
> But once the cells have been colored on the basis of conditional formatting,
> I want to keep the colours and remove the conditional formatting from the
> cells, because now if I MOVE just a part of that data to someplace else in
> the sheet, it rechecks for those conditional formatting checks as per the
> new location of the data and reformats the data.
> 
> Please help.
> 
> Thanks
> Ashish
> 
> "Ron_D" <anonymous@discussions.microsoft.com> wrote in message
> news:9CA02A5E-493F-496F-8ED5-3ED6911CDA0F@microsoft.com...
> > Ashish,
> > Are you totally opposed to keeping the conditional formatting?  You can
> make all the cell references in the conditional formatting window absolute
> by typing "$" to the row and column address.  This will ensure the
> formatting stays the same when you move the cells even with the copy method.
> >
> > Otherwise, I don't think there are ways to keep the formatting when you
> eliminate the conditions.
> >
> > Ron_D

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/17/2003 4:28:27 PM
Ashish,

I can't think of a direct way to apply a particular format of conditional
formatting to the cells as regular (permanent) formatting.  can you use the
same conditional formatting at the destination? Or, instead of using
conditional formatting, you could run a macro that applied normal
formatting.  Then it would copy and paste.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Ashish Chamaria" <ashishchamaria@hotmail.com> wrote in message
news:OqyRPTGxDHA.2408@tk2msftngp13.phx.gbl...
> Hello,
> I am using MS-Office-2000.
> I have applied formula-based conditional formatting on a range of cells.
> Now, I want to remove the Conditions but wanna keep the formatting on the
> data as it is. This will help me in Moving the data around the sheet
without
> loosing the formatting.
> Is there any way of achieving this ?
>
> Thanks,
> Ashish
>
>


0
nowhere8060 (363)
12/17/2003 5:21:16 PM
Actually I had to apply some colour-codes on the data based on certain
conditions. The datasheet that I have contains around 6000 cells of data. So
I created a conditional formatting formula which I copied on all the 6000
cells to get the desired color formatting for different kinds of data. The
conditional formatting formula itself is dynamic as it is not based on any
one particular cell and thats why I cant use absolute referencing in this
formula as it later has also to be applied on all the 6000 cells.

But once the cells have been colored on the basis of conditional formatting,
I want to keep the colours and remove the conditional formatting from the
cells, because now if I MOVE just a part of that data to someplace else in
the sheet, it rechecks for those conditional formatting checks as per the
new location of the data and reformats the data.

Please help.

Thanks
Ashish

"Earl Kiosterud" <nowhere@verizon.net> wrote in message
news:OTT1vIMxDHA.536@tk2msftngp13.phx.gbl...
> Ashish,
>
> I can't think of a direct way to apply a particular format of conditional
> formatting to the cells as regular (permanent) formatting.  can you use
the
> same conditional formatting at the destination? Or, instead of using
> conditional formatting, you could run a macro that applied normal
> formatting.  Then it would copy and paste.
>
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Ashish Chamaria" <ashishchamaria@hotmail.com> wrote in message
> news:OqyRPTGxDHA.2408@tk2msftngp13.phx.gbl...
> > Hello,
> > I am using MS-Office-2000.
> > I have applied formula-based conditional formatting on a range of cells.
> > Now, I want to remove the Conditions but wanna keep the formatting on
the
> > data as it is. This will help me in Moving the data around the sheet
> without
> > loosing the formatting.
> > Is there any way of achieving this ?
> >
> > Thanks,
> > Ashish
> >
> >
>
>


0
12/18/2003 5:59:42 AM
Hi Dave,
Thanks a lot for your reply.
Its just that I wanted to share a workaround which I discovered for
achieving the task.

Just copy the data (that has been conditionally formatted) and paste it in
MS-Word document. Now, recopy the same data from MS-Word and paste it in
MS-Excel.

The formatting stays but the conditions are gone !!!

Thanks
Ashish

"Dave Peterson" <ec35720@msn.com> wrote in message
news:3FE0842B.6A3038C6@msn.com...
> This doesn't look trivial to me.
>
> But Chip Pearson did most of the work.
>
> I went to his site:
> http://www.cpearson.com/excel/CFColors.htm
>
> And stole his ActiveCondition function.
>
> (I did make a change to it because of an oddity in excel:  See John
Walkenbach's
> site:
> http://j-walk.com/ss/excel/odd/odd07.htm to see more information.)
>
> I got help from both John and Bernie Deitrick on how to overcome this
bleeping
> oddity!
>
> (Both John's and Bernie's tip seemed to work ok for me.  I included (but
> commented out) John's version.  I used Bernie's (simply because it was
more
> simple!).  (I think I would have had to activate a different worksheet in
either
> case.  And if I have to activate a worksheet, I might as well just select
the
> cell!--it goes against a lot of things I've learned here, but you gotta do
what
> works.)
>
> Chip's code is the workhorse.  It determines which condition is active.
The
> code that calls it just removes the non-active format conditions and
replaces
> the activecondition with True.  (so it always stays active).
>
> So no matter what's in the cell, the conditional formatting that was there
will
> always apply (well, until you change it.)
>
> I've included Chip's code here only because of the slight changes I made.
>
> (The notes and most of the procedure came from a similar request:
> http://groups.google.com/groups?threadm=3DEFDE80.280201E0%40msn.com)
>
> And had this followup:
>
> One thing that I didn't think of (and I hope that it doesn't affect you).
>
> If you have cells that evaluate to errors (1/0, ref, n/a type stuff), then
the
> ActiveCondition function blows up.  The code uses a lot of .values.
>
>
> ===============
> Try this against a copy of your worksheet--just in case.
>
> Option Explicit
> Sub testme()
>
>     Dim myRng As Range
>     Dim mycell As Range
>     Dim myCell_AC As Long
>     Dim wks As Worksheet
>     Dim i As Integer
>     Dim startCell As Range
>
>     Set startCell = ActiveCell
>
>     For Each wks In ActiveWorkbook.Worksheets
>         With wks
>             Set myRng = Nothing
>             On Error Resume Next
>             Set myRng = .Cells.SpecialCells(xlCellTypeAllFormatConditions)
>             On Error GoTo 0
>
>             If myRng Is Nothing Then
>                 'do nothing
>             Else
>                 application.screenupdating = false
>                 For Each mycell In myRng.Cells
>                     If mycell.FormatConditions.Count = 0 Then
>                         MsgBox "something bad happened with " & _
>                             mycell.Address(external:=True)
>                         'do nothing
>                     Else
>                         myCell_AC = ActiveCondition(mycell)
>                         If myCell_AC = 0 Then
>                             mycell.FormatConditions.Delete
>                         Else
>                             For i = mycell.FormatConditions.Count To 1
Step -1
>                                 If i = myCell_AC Then
>                                     mycell.Interior.ColorIndex _
>                                          = mycell.FormatConditions(i) _
>                                                  .Interior.ColorIndex
>                                 End If
>                                 mycell.FormatConditions(i).Delete
>                             Next i
>                         End If
>                     End If
>                 Next mycell
>                 application.screenupdating = true
>             End If
>         End With
>     Next wks
>
>     Application.Goto startCell
>
> End Sub
>
> Function ActiveCondition(Rng As Range) As Integer
>
> Dim Ndx As Long
> Dim FC As FormatCondition
> Dim tmpRng As Range
>
> Set tmpRng = Rng
> Set Rng = Nothing
> Set Rng = tmpRng
>
> If Rng.FormatConditions.Count = 0 Then
>     ActiveCondition = 0
> Else
>     For Ndx = 1 To Rng.FormatConditions.Count
>     Set FC = Nothing
>     Set FC = Rng.FormatConditions(Ndx)
>     Set FC = Rng.FormatConditions(Ndx)
>     Select Case FC.Type
>        Case xlCellValue
>            Select Case FC.Operator
>                Case xlBetween
>                    If CDbl(Rng.Value) >= CDbl(FC.Formula1) And _
>                       CDbl(Rng.Value) <= CDbl(FC.Formula2) Then
>                       ActiveCondition = Ndx
>                       Exit Function
>                    End If
>
>                Case xlGreater
>                    If CDbl(Rng.Value) > CDbl(FC.Formula1) Then
>                        ActiveCondition = Ndx
>                        Exit Function
>                    End If
>
>                Case xlEqual
>                    If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
>                       ActiveCondition = Ndx
>                       Exit Function
>                    End If
>
>                Case xlGreaterEqual
>                    If CDbl(Rng.Value) >= CDbl(FC.Formula1) Then
>                        ActiveCondition = Ndx
>                        Exit Function
>                    End If
>
>                Case xlLess
>                    If CDbl(Rng.Value) < CDbl(FC.Formula1) Then
>                       ActiveCondition = Ndx
>                       Exit Function
>                    End If
>
>                Case xlLessEqual
>                    If CDbl(Rng.Value) <= CDbl(FC.Formula1) Then
>                       ActiveCondition = Ndx
>                       Exit Function
>                    End If
>
>                Case xlNotEqual
>                    If CDbl(Rng.Value) <> CDbl(FC.Formula1) Then
>                        ActiveCondition = Ndx
>                        Exit Function
>                    End If
>
>                Case xlNotBetween
>                    If CDbl(Rng.Value) <= CDbl(FC.Formula1) Or _
>                       CDbl(Rng.Value) >= CDbl(FC.Formula2) Then
>                      ActiveCondition = Ndx
>                      Exit Function
>                    End If
>
>                Case Else
>                    Debug.Print "UNKNOWN OPERATOR"
>          End Select
>
>
>       Case xlExpression
> '        John Walkenbach 's excel oddity page
> '        http://j-walk.com/ss/excel/odd/odd07.htm
> '        describes the problem
> '
> '        Bernie Deitrick's tip about selecting the cell first to make
formula1
> '        work correctly works fine, too.
> '
> '        from John's site:
> '          Dim F1 As String
> '          Dim F2 As String'
> '          Rng.Parent.Activate 'make F2 formula work with activecell.
> '          F1 = Rng.FormatConditions(1).Formula1
> '          F2 = Application.ConvertFormula(F1, xlA1, xlR1C1, , ActiveCell)
> '          F1 = Application.ConvertFormula(F2, xlR1C1, xlA1, , Rng)
>
> '        From Bernie's tip
>          Application.Goto Rng
>
>           If Application.Evaluate(FC.Formula1) Then
>              ActiveCondition = Ndx
>              Exit Function
>           End If
>
>      Case Else
>            Debug.Print "UNKNOWN TYPE"
>      End Select
>
>   Next Ndx
>
> End If
>
> ActiveCondition = 0
>
> End Function
>
>
>
> Ashish Chamaria wrote:
> >
> > Actually I had to apply some colour-codes on the data based on certain
> > conditions. The datasheet that I have contains around 6000 cells of
data. So
> > I created a conditional formatting formula which I copied on all the
6000
> > cells to get the desired color formatting for different kinds of data.
The
> > conditional formatting formula itself is dynamic as it is not based on
any
> > one particular cell and thats why I cant use absolute referencing in
this
> > formula as it later has also to be applied on all the 6000 cells.
> >
> > But once the cells have been colored on the basis of conditional
formatting,
> > I want to keep the colours and remove the conditional formatting from
the
> > cells, because now if I MOVE just a part of that data to someplace else
in
> > the sheet, it rechecks for those conditional formatting checks as per
the
> > new location of the data and reformats the data.
> >
> > Please help.
> >
> > Thanks
> > Ashish
> >
> > "Ron_D" <anonymous@discussions.microsoft.com> wrote in message
> > news:9CA02A5E-493F-496F-8ED5-3ED6911CDA0F@microsoft.com...
> > > Ashish,
> > > Are you totally opposed to keeping the conditional formatting?  You
can
> > make all the cell references in the conditional formatting window
absolute
> > by typing "$" to the row and column address.  This will ensure the
> > formatting stays the same when you move the cells even with the copy
method.
> > >
> > > Otherwise, I don't think there are ways to keep the formatting when
you
> > eliminate the conditions.
> > >
> > > Ron_D
>
> -- 
>
> Dave Peterson
> ec35720@msn.com


0
12/19/2003 10:54:47 AM
Reply:

Similar Artilces:

Date formatting on charts
I have a simple line chart (in PPT 2007) with dates going along the x-axis. They are m/d/y format - I cannot seem to change them to another format for the life of me! I am going to format-axis-number-date and cannot change the format at all! Does anyone know how this procedure - so simple in PPT 2003 can be achieved? Help and thanks! -- Marc Press with your right mouse button on the chart and choose Format Axis. From the new menu go under Number - you'll have a list of different formats available. Kristiina I help with Excel and PowerPoint http://www.officetodo...

Help with Event formatting?
Hello, I have a form with a drop down combolist. When Verification is chosen from it, I want to do an Event, AfterUpdate. I want it to first see that "Verification" has been chosen, then for it to check the current SSN against the SSNs in a 2nd AddressTable. If there is no SSN match in the 2nd table I'll need a Subform to pop up and take the address information, and then a Generate Letter/Report button can be used, with all the extra info being saved to the 2nd table. If there is a SSN match, that means the address is already in the 2nd table, and a Report/Letter is...

How do I preserve data formatting in a pivot chart?
After establishing all desired fonts, text alignments, and data series colors/patterns within a pivot chart, I cannot preserve the settings. As I change a selection in a page field button on the chart, the data series revert back to default color settings. Any autoformat options I can toggle on/off? This is a known problem with pivot charts. There's some information in the following MSKB article, which suggests recording a macro as you apply the formatting: Changing a PivotChart removes series formatting in Excel http://support.microsoft.com/?id=215904 dblack wrote: > Aft...

Date format clarification
When a user selects the 14-Mar-01 standard date format in Excel 2007, then selects the custom format option, this is the standard format that displays: [$-409]d-mmm-yy;@ What is the significance of the prefix "[$-409]" included with this standard format? Why is that prefix even necessary and is it necessary to include the prefix for all custom date formats we create? Thank you for any help. Its a number format code. See: http://office.microsoft.com/assistance/hfws.aspx?AssetID=HA010346351033&CTT=6&Origin=EC010272491033 -- Gary''s Studen...

Conditional Formatting -- Need VBA Code
Hi, Can someone help me out with some starting VBA code for conditional formatting? (I need more than the three conditions that the built-in function permits.) I essentially just need the text of a combo box on a continuous form to change color depending on which of seven options is selected in that same combo box. Thank you in advance! Kim Kim, Have a look at... http://msdn.microsoft.com/en-us/library/aa139965(office.10).aspx -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II ...

Selecting Columns conditionally?
Good morning all, Just a quick question - I have a table that looks something like this... The first row are the field names as stored in access, everything after that is the data stored in the table. NodeName T1F1 T1F2 T2F1 T2F2 T3F1 T3F2 NodeName (SG) (AQ) (%) (%) (6) ($) ABAO .62 39 .69 .01 39.5 867 ABCD .61 39.1 .68 .05 30.1 666 This table is being created by importing a text file. The 1st Record in this table, contains the "actual" field names. Due to t...

Need VBA for Conditional Format of Row
Hi guys, I need VBA code to add to an existing macro. If column H has an "x" in it then I need that entire row highlighed as color 42 (light blue). Below is what I tried but it isn't working. Any help would be appreciated. Dim cfrange As range Set cfrange = range("h7:h700") If cfrange = "x" Then range("A:G").Interior.ColorIndex = 42 End If -- Danielle :<) for each c in range("h7:h700") if ucase(c)="X" then rows(c.row).interior.colorindex=42 next c -- Don Guillett Microsoft MVP Excel SalesAid S...

Excel 2007 & Custom Number Format Limitations?
I'm used to getting those error messages in Excel about too many custom number formats and I was wondering if this is still the case with Excel 2007 as I'm planning to migrate soon. Thanks. Funny you should ask that as I just read the message from Venkat timestamped 2 hours later than yours. The limit to unique cell formats was increased a lot in Excel 2007 and, absent his message, I'd have said case closed. But VerKat said he had a workbook with that problem in Excel 2003 that when opened in Excel 2007 lost all its cell formats. Go figure. I have both versions and I...

formating multiple selection in report
I am displaying a multiple selection and it lists them out with a comma after each one. I want it to to to the next line after the comma. currently like this item one, item two, item three, item four I want to look like this item one, item two, item three, item four any suggestions? the items are various lengths. On Wed, 9 Jan 2008 16:24:30 -0800, Arnold Klapheck wrote: > I am displaying a multiple selection and it lists them out with a comma after > each one. I want it to to to the next line after the comma. > > currently like this > item one, item two, item > three...

Automatically formatting and printing messages
I have an html form that sends an Email to my Outlook account. In the Email is pertinent information that I would like to process in the following way: -Format the body of the Email using a Word Macro/Auto-Format template -Automatically Print the message -Move the message to a folder entitled "Processed" I am thinking that I can use an Outlook rule that would touch-off an Outlook macro to select the body of the Email. The Outlook macro would then call MS Word, passing the name of a Word macro as a parameter which Word would execute on the contents of the clipboard and then close ...

Formatting cell based upon values w/in a selection
Background: I have a spreadsheet which tracks 10+ training course dates for 100+ individuals. All dates have conditional formatting applied to turn red if the date is <=TODAY(), yellow if btwn TODAY()+1 and TODAY()+30, and amber if the date in the cell is btwn TODAY()+31 and TODAY()+60. Dilemma: Column A, for each individual, needs to reflect their overall status (preferrably using the stoplight icon set) in their training. If any of their items are in red, Column A would show the red stoplight...if no items are red but yellow or amber dates are present, Column A would show the ...

Word freezes
I am running Word 2004 on an iMac using OS 10.5.8 with 5 GB RAM As a writer I often have multiple Word documents open. Yet I am plagued by Word freezing on me at unexpected times. This is different from a crash when trying to past too much material, such as from a PDF or Web page. At random times, Word simply will not respond to keystrokes or new input. But I have learned that if I find the culprit document, click on the red Save button and then cancel, all of the documents can be accessed again. Any idea what is causing this and how I can prevent it? Hi Jack: You have not state...

Formatting an index with tab stops?
I am trying to create a nomenclature page for my master's thesis using an index. I essentially want three columns in the nomenclature - the abbreviated term, the expanded version, and the page number of the first instance used. I would like the abbreviated terms to be aligned at 0" on the ruler; the expanded version to be left aligned beginning at 1.5"; the page numbers to be right aligned at 6.5". Getting the page numbers aligned is not an issue. My problem is establishing the second column. Below is an example with the default marked entry text within the field ...

2007 Control Reply Format Style
This is a multi-part message in MIME format. ------=_NextPart_000_00EB_01C8E415.D28CFB40 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Is there any way to control reply format without having to select the = reply text and apply a quickstyle. I tried NormalEmail.dotm but it = changes Normal.dotm and the incoming email message. I have HTML turned = on. ------=_NextPart_000_00EB_01C8E415.D28CFB40 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//...

IF and conditional sum, one result
Hello everybody. I am trying to do something with a spreadsheet and no sure how to work the formula. I have seven columns and seven rows. Th column starts at F and the rows start and 1. The formula I have i this: =IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5)))) This is in cell K6. I would like this formula to also be able to loo at cells L6 and L7, and if L7 is larger than L6, subtract 1 from resul of the formula in K6. Can something like this be done? Thank you i advance for any help that ca...

conditional counting of values in Excel 2003
Hi everybody I'm trying to make a statistical analisys of the answers to a questionnaire like this: 1. Where are you from? (US) (IT) (DE) 2. Look at this picture. How much do you agree with each of the following statements (1=totally disagree, ..., 4=totally agree) ? 2.a) It's funny (1) (2) (3) (4) (5) ... 2.z) It's obscene (1) (2) (3) (4) (5) Answers have been collected like this (in columns, starting from B): | A | B | C | D | E | F | G | ----|------------+----+----+----+----+----+----+ 1 |Nationality | US | US | IT | US | IT | DE | ----+--------...

Format Row
How do you format text and fill colors to show diagonally in a row. Ex. For a column header Thx, Tanya You can type something in a cell, then Format|Cells|Alignment to display the text on an angle (use the Orientation part to tilt the text). The fill colors fill the cell. But if it's really important, you might be able to add some shapes (via the Drawing toolbar) like triangles that fit over your cell. Then color each triangle separately. You can turn off the lines on the triangles. And if you use the alt key when you're positioning the shapes, they'll snap-to the cell'...

Outlook send format
When replying on a E-mail the format of the e-mail changes. In stead of answering the E-mail in RTF the mail is answered in normal text. Is there a setting where it is posible to mark that the mails should be foced to send the mail in RTF? It functions when creating a new mail, it is only when replying on a mail. When replying or forwarding, Outlook will always try to use the format that the original message was in. For example, if the original message is in Plain Text, Outlook will try to reply in Plain Text. You can force Outlook to always send to a certain contact in Rich Text mo...

Excel Cell Formats
How do I format cells in the Canadian postal code format? What's to format? Simply treat it as text. -- Regards, Fred Please reply to newsgroup, not e-mail "Ron" <Ron@discussions.microsoft.com> wrote in message news:6CE059EE-8938-4915-B364-2D0D1F1D9449@microsoft.com... > How do I format cells in the Canadian postal code format? By typing the space. -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm &q...

Format Issue with Dates
I have an excel spreadsheet that the column cells are formatted as DATE: 03/14/07. When you enter in a date like 8-8-07, it gives #######. If you look at the entry it puts an equal sign in the entry at the beginning and excel try to calculate 8-8-07. If you take out the equals sign it goes right back in. How can this be corrected. Not xl2007: Tools/Options/Transition/Uncheck Transition Formula Entry Bob Umlas Excel MVP "msmith" <msmith@discussions.microsoft.com> wrote in message news:0B9BE11F-8B15-4D42-8CD4-C5F7200F28C9@microsoft.com... >I have an excel spreadsheet...

Excel formulas / formatting
This has come up on an excel spreadsheet. How do I change this into a number? 1.00311E+14. Jazzy1980 wrote: > This has come up on an excel spreadsheet. How do I change this into a number? > 1.00311E+14. That is a number, equal to 1.00311 with the decimal point moved to the right by 14 places. You can format it as a fixed point number if you wish by selecting it's cell and clicking on Format>Cells. Then tell it to use "number" format rather than "scientific". It will probably just show you a bunch of ##### which means that your column isn't wide...

Might be simple... Cell Formating.
Is it possible to format a cell where numbers are one color and alpha is another? I'm using Excel 2007? At times it's hard to tell the difference between L's and Ones ( l1 ) Thanks, Mark If you want digits and letters within one cell to have different colours I cannot help other than to suggest you experiment with different fonts. To make display text in one colour, use conditional formatting with formula =ISTEXT(A1) and set the required colour. This will also display the selected colour if you inadvertently press l (lower case L) when you want 1 (digit 1) best wishes ...

conditional number formats
Does anyone know how to link the number format of a cell on the value of another cell by using the custom number format tool and not VBA? For example: if cell A1 = "GBP" then value in cell B1 will be displayed as either "� <value>" or "GBP <value>" So if the user then enters "EUR" in cell A1, then B1 will automatically change to either "� <value>" or "EUR <value>". Thanks. --- Message posted from http://www.ExcelForum.com/ You would need VBA for that. -- Regards, Peo Sjoblom "I_need_help >&...

conditional formatting question #9
I have a spreadsheet that gets updated monthly and looks like the example below Jan Feb march April May June 21,000 23,500 22,224 22111 20,111 25,000 I've asked this question before and have gotten great tips but each month when refesh the pivot table it looses all the conditional formatting and I start over again. The conditional formatting needed is if #'s go down for three consecutive months in a row they are highlighted in red. I need the first month of dropped revenue example above would be March in yellow and then t...

how do i use multiple conditional formats in one cell?
I need to make difrent "words" in one cell make number values in a difrent cell tyson CF will allow up to 4 formats per cell, including the default format. However, CF will not place number values in cells. Can format only as in Fonts, Borders and Patterns. What would you like to have done? Perhaps there is another method. A worksheet function such as one of the LOOKUP functions or MATCH. Gord Dibben Excel MVP On Fri, 21 Jan 2005 14:55:03 -0800, "tysonstone" <tysonstone@discussions.microsoft.com> wrote: >I need to make difrent "words" in one ...