Conditional Formating. Remove condition, keep format?

I have compaired lists using condition formating, now I need to work with one
of these lists, but need to know the information the conditional format has
given me.
Once I copy out the list, I loose the conditions and thus the format.
Is there a way to convert the conditional formating to standard format?


Hey guys! I am working on a very large spreadsheet, with links scattered throughout. I would like to remove some of those links without losing the cell formatting. I've tried using the Selection.Hyperlinks.Delete call, but it removes all the formatting as well. Anyone know how I can do this? Thanks in advance for any assistance!

I am using excel 2007 - I need the extra available columns in Excel 2007 for my application.

I have a spreadsheet that has a significant amount of conditional formatting. I want to copy a range from this spreadsheet to another spreadsheet. In the new spreadsheet I want to remove the conditional formatting rules whilst retaining the actual formats those rules generated.

Format painter and "paste special - format" copies any formats as well as the conditional format rules.

Is htere a method within Excel to do this or can someone suggest a workaround?

I like the way that Excel 2007 handles conditional formatting for
overlapping ranges and such, but sometimes I have to save files
in .xls format. Invariably I get a warning that the conditional
formatting will not render correctly in older versions of Excel.

Is there a version of "Paste Values" for conditional formats that
applies the formatting directly to the cells? It would not longer keep
up with changes, but for presentation purposes it would work just
fine.

For example, I have a table of about 2200 rows by 50 columns. Going
down a particular column, most of the cells are zero, and the nonzero
values tend to occur in clumps. Using number formatting 0.0_);(0.0);;@
I blanked out the zero values. Using conditional formatting, I was
able to highlight the first cell of a cluster one color, the middle
cells a second color, and the last cell (before going back to zero) a
third color. I also have some conditional formatting that marks
certain rows, and lots of other stuff going on to help with data
visualization. In all there are 8 conditional formats, and it looks
just the way I want it to look.

I'd like to be able to save this as an Excel 2003 file and keep the
formatting. I know that Excel 2003 can't handle all these conditions,
but I would like a way of just applying the formatting-as-displayed to
the cells before saving. Something analogous to how "Paste Values"
will give you the value-as-displayed without the underlying formula.

Thanks!

Hi,

I see from searching the forums that similar questions to this have been posted before, but can not find a satisfactory answer. Here's the problem: I have a large excel sheet that has conditional formatting for column heads that reference a condition at the top of the sheet..eg. if cell value (at top of sheet) is equal to "f", make column head red and bold. This spreadsheet gets split up in to mulitple smaller excel tables without the condition lines that are present in the large file. I want to maintain the conditional format in the smaller tables. I've tried copy pasting the conditional format and I've tried copy pasting format then removing the conditional format. Trouble is the formatting is still lost once the spreadsheet is divided up. Is there any solution to this problem?

Hello - - I have a file that I would like to apply some conditional formating to but when I do, it removes any other formats. I've dummied up a file and attached it. You'll see that colummn F is Blue and M is yellow. what I want to do is BOLD the row when the word 'Jeff' is found in column B, so in this example A8 to N8 would be BOLD, I need to have the blue and yellow remain. When I copy my conditional format through the sheet, the blue and yellow shade goes away. I'll have over 50 of sheets like this one and 'Jeff' will appear in different rows. I'd rather not use VBA so I was thinking there might be away to do this with conditional formatting...thanks to all you Excel Gurus!!!!

Hello Friends,

I have this issue with conditional formatting. I want to apply a conditional formatting for a cell, but want to apply the condition referred from a cell value.

For e.g: I want to Apply format to cells in Sheet1. But the condition will be in a different sheet (Sheet2) where it is mentioned as any of the following:100="High"="Low"etc.,

How do I refer this condition indirectly to conditional formatting Formula

Thanks in advance,

Regards,
RK.

Hello, fellas!

You all seem to be extremely intelligent in here, so I thought I'd ask this question since I can't seem to find an answer ANYWHERE!! I even spent $156.85 in the Mr. Excel store in the hopes that something that I was purchasing would give me the answer I need.

I need a way to color some text gray. We have facility types that consist of two-letter codes and I want five of them to pass through uncolored. But the other 30 or so should be colored gray.

Right now, the majority of these codes in my file are ('AL','IL','HS'), so I entered three conditions:

If equal to 'AL', format Gray,
If equal to 'IL', format Gray, and
If equal to 'HS', format Gray.

However, I want to add a few more of these conditions, but Excel is limited to 3 conditions.

I was using the "Cell Is" selection in the pull down box for the last example, but there is something called "Formula Is" and I was imagining that I could enter some sort of formula in there to say:

"WHEN CELL IS NOT IN ('SN','CN','IC','SA','HN') THEN COLOR CELL GRAY."

Does this make sense to anyone? It seems like such a simple question compared to everything else I've seen in here in the last week that I've known about this site, so hopefully someone out there can help me.

Thanks!
Matt Albright
malbright@directsupply.net

Hi everybody

I have three workbooks and want to streamline them a little more.

The problem I am running into is kindof a complicated one.

I have a couple questions.

Is there a way to have conditional formatting make its condition based on the actual format of another cell?

The other question is related and is:
Can this cell be in another workbook?

for example:
in workbook1
a1=greenbackground(yellowtext)

b1=graybackground(blacktext)

in workbook2

i want a1 to match the formats of a1 in workbook1
i want a2 to match the formats of b1 in workbook1

can i put somethign like
=format(worksheet1!a1) in the conditional format box for a1 in worksheet2?

Greetings,

I checked previous posts and found, what I thought was, an answer to my
problem with conditional formatting based on nested IF's. No Joy, I'm still
doing something wrong. Help?

In cell I69 is a percentage of achieved sales.
I want to color the cell I71 either red, yellow or green, based on the % in
I69.

Cell I71 contains no data. It's just a visual aid to draw attention to the
performance of the salesperson in Column I.

My scale is this:
If I69 is 0 to 92% Format I71 background Red
If I69 is 93 to 97% Format I71 background Yellow
If I69 is 98 to 100% Format I71 background Green

This is what I have done so far:

Selected cell I69
Selected Format > Conditional Formatting

Condition 1 - Formula is =I6992,I69=98 Format...Green

Well, it's not working. No matter what the percentage is in cell I69,
cell I71 is stuck on Red. Like a bad stoplight.

Where am I wrong in my formatting?
Thanks a lot.

--
Mike
Jacksonville, Florida

Greetings,

I checked previous posts and found, what I thought was, an answer to my
problem with conditional formatting based on nested IF's. No Joy, I'm still
doing something wrong. Help?

In cell I69 is a percentage of achieved sales.
I want to color the cell I71 either red, yellow or green, based on the % in
I69.

Cell I71 contains no data. It's just a visual aid to draw attention to the
performance of the salesperson in Column I.

My scale is this:
If I69 is 0 to 92% Format I71 background Red
If I69 is 93 to 97% Format I71 background Yellow
If I69 is 98 to 100% Format I71 background Green

This is what I have done so far:

Selected cell I69
Selected Format > Conditional Formatting

Condition 1 - Formula is =I69<=92 Format... Red
Condition 2 - Formula is =AND(I69>92,I69<98) Format... Yellow
Condition 3 - Formula is =I69>=98 Format...Green

Well, it's not working. No matter what the percentage is in cell I69,
cell I71 is stuck on Red. Like a bad stoplight.

Where am I wrong in my formatting?
Thanks a lot.

--
Mike
Jacksonville, Florida

Is is possible (via vba) to Insert a new condition before existing conditional formatting conditions.
My existing cells with cond format have max of 2 conditions at present.
Due to the large number of cells/columns involved with varying conditional formatting requirements, i would like to insert a new condition (as condition 1 - as having precedence overs lower existing conds) to each cell in a given row, then i can copt down as required.

The example file attached has -
- cells in row 2 of columns titled data 1, data 2 & data 3 with required result in condition 1 (make black if Action = Ignore).
- cells in row 3 of columns titled data 1, data 2 & data 3 has the an example of an existing condition 1.

Irrespective of existing conditions 1 or 2, is it possible to insert a new condition via vba or add, then re-number the last condition to become the first, and increment other positions to + 1

Compartmentalising each cell in "X" no of parts and formatting it conditionally.!

I am making a small application which shows various Activities/Processes involved in any project Vs Timeframe..

In this application the aim is to correctly colour the Columns for each Activity falling in the particular dates which are the Column Headings.

Now, is it possible to divide each cell into 7 equal parts and arrange the colour formatting in the respective block..

For Ex: Let say the Activity : ->
Feasibilty Study starts on 10-Jan-10 and ends on 21-Jan-10
and the Date Column Heading Starts from 8-Jan-2010 and 15-Jan-2010
Now, this entire Acitvity is spread in these two columns however if we look carefully it should be shown as 5 filled blocks of space in the 8-Jan-2010 and remaining 7 block of space in the next column in 15-Jan-2010...

I have created a conditional format that highlights a series of cells red when 2 dates are 14 days apart. =OR(($H$22-$I$22+1)>14,($I$22-$H$22+1)>14)

However as is now the formula is making all of the empty cells red. I'm trying to prevent this with condition 2. But I dunno what that would be. I want the empty cells, f12:i50 left alone unless there are dates in the h and i columns that are 14 days apart.

condition 1 = make cell f 12:I12 red if h12 and i12 are 14 days apart (example)
condition 2= ignore condition 1 if h column has no date (i column will always have todays date, h column is inputed by user).

I have a sheet in which I made four conditional formats and four custom number formats.
The conditional formats are each applied to cell C90. I used New Rule > Use a formula to determine which cells to format.
The Equation for the first is:
The Equation for the second is: 
	

	
And so on.

The custom number format corresponding to the first one is:
The custom number format corresponding to the second one is:
And so on.

When I try them out, they don't work the first time you try. When I change the value of E91 from 3 to 4, C90 still has "(#3)".
If I again put 4 into it and hit enter, C90 will change to "(#4)".
OR
If I enter 5 into C90 after having 4 in there, it will then change to "(#4)"
But if I go straight from 3 to 5, it will stay at "(#3)"
I tried assigning different fills to go along with each custom number format. The fills change when they are supposed to.
I also tried assigning pre-defined number formats to the cells and they are also not working correctly
There must be some sort of problem with number formats not updating right away.

Hi all
Please can any one help I am trying to format a cell using another cells format as the criteria, EG if cell =Sheet1!A1 is blue fill with red letters then cell Sheet2!B4 is same format other wise default format.
I have tried conditional formatting but it seems to be conditional to formula or cell value only.
Thanks
Chubbers

I understand that if you want to keep certain formatting you've made for a Pivot table, you have to turn off auto format table and keep the Preserve Formatting checked.

However I notice that if I refresh the pivot table, a lot of my present formatting vanishes. It does not completely undo the formatting, just a lot of it.

Am I missing something or is there no way to really keep all the formatting I've done?

Is there a way to keep formatting during an import of data?

I am looking to keep all formatting on a worksheet but everytime I import data from a DB, the formats are cleared. Before I create a macro for the formatting, I thought I might ask if there was something I could turn off or change to prevent this.

How do I remove time from a date and time field? Format removes the display
but I need to filter to just show unique based on the day. Since the time is
part of the field, I am not able to receive the filtering by day.

Excel 2000

I have two files, one master and one created by opening a text file. The
master file has a list of unique product codes and descriptions in two
columns, the text that is opened has the product codes and I add during the
opening process a look up to the product code to return the product
description, this all works fine. The text file after opening is 7 columns,
product code, product description and 5 columns of numbers.

What I need to do is format the cells (numbers) in the last 5 columns of the
newly opened text file. My thoughts are to add a third column to the master
file whereby I'll have 3 columns: product code, product description and
number format, in the number format column I would format the cell with the
required number format. My issue is, how using VBA do I format the cells in
the newly opened text file where the product code matches that of the
corresponding product code in the master file. The text file can have many
of the same product codes and in some instances, not all of the product
codes.

I had also thought of Conditioning Format but alas, this doesn't cover
number formatting.

Any pointers, snippets of code most welcome.

Thanks, Rob

Change Email Addresses from Vertical Row Formation, to Horizontal Column Formation.
Currently I have email addresses in Word file like this:

(email addresses removed by admin - you should NOT enter post real email addresses in public forums as they will get picked up by SpamBots and those people may be inundated with Spam)

When I copy it into Excel Spread sheet, they all fall into under A1 or some other A50 box. I tried to Transpose, but that did not seem to work. I found this Macro Formula listed at the bottom. Is this even doable? TransposeRows = Range("A65536").End(xlUp).Row

Application.ScreenUpdating = False

For i = 1 To TransposeRows

Range(Cells(i, 1), Cells(i, 4)).Copy

If i = 1 Then

Range("F65536").End(xlUp).PasteSpecial Paste:=xlAll, _

Operation:=xlNone, SkipBlanks:=False _

, Transpose:=True

Else

Range("F65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlAll, _

Operation:=xlNone, SkipBlanks:=False _

, Transpose:=True

End If

Application.CutCopyMode = False

Next i

Application.ScreenUpdating = True

End Sub

Cell Format Overrides to Date Format every time file is opened.

how to get rid of the date format override?

how to get it to accept the format given.

thanks.

How do i put comma in a number in lakhs-crores format rather than in
million-billion format as lakhs-crores format is prevalent in India

--
Sumit
------------------------------------------------------------------------
Sumit's Profile: http://www.excelforum.com/member.php...o&userid=29520
View this thread: http://www.excelforum.com/showthread...hreadid=492198

I have a certain cell that is used to input a date. I want to be able to
push a button and copy this date to antoher cell, however I need the
formatting to change from the original date format to a text format that goes
yyyy-mm-dd. I've tried using a custom format and then changing that to text
but the date just changes to a number. Any suggestions?

Thanks

Adam Bush

Hai,

I am Suresh Babu and I need to help in converting the Date format into Week number format

For example:

Existing date format is 9-Sep-2010 and I wanted to convert as 1036.4 (10-year, 36-Week & 4-day)

Please help

Regards,
Suresh Babu MS