Great tip. I find the every other shading to be visually busy, but have found every fourth row to be as meaningful to the eye. Your brain sees one of four conditions:
1)The row is shaded
2)The row is just below the shaded row
3)The row is not shaded
4)The row is just above the shaded row

It is a little cleaner look IMHO- but a very useful tip from the Juice team... thank you.

I tweaked the cell shading as folows:

=ROW()/4=INT(ROW()/4)

Where 4 = the Nth row I want to have shaded... shade every 6th row thusly:

=ROW()/6=INT(ROW()/6)

Chris:

I take this as another interesting area to explore further :-)

1. With Copy/Paste Special [Formats] you can copy just the formats, and not the formula.

2. The formula may refer to other cells than the one to be formatted. For instance, if cell A2 contains a name of a sales rep (use a list to avoid typo's!) the cell containing the total sales (say E7) can be coloured depending on the name in A2 selected. So the formula for cell E7 is =$A$2="Chris", etc.

3. The concept can be used to highlight values of interest in a large spreadsheet crowded with numbers. This can be a minimum, maximum, a condition met (larger than X), profits/losses, etc.

4. A side path: conditional formatting (i.e. formatting depending on certain conditions or constraints) also can be created for charts. You can colour certain data points, adding an arrow to points of interest, or whatever your imagination comes up with.

5. Yet another side path: with IF statements you can make conditional texts. I use this for my financial report where the numbers trigger the relevant comments. To explain how this works, say cell D3 contains the financial result for the month. In cell A7 I put my comments like this. =IF(D3 "larger than" 0, CONCATENATE("This month returned a nice profit of ",TEXT(D3,0)),CONCATENATE("This month saw a loss of ",TEXT(D3,0))). [NB: "larger than" to be replaced by the sign, but this isn't supported by the blog software, hence put as text here]. In turn, this can be combined with conditional formatting, e.g., in case of a profit the text is blue, in case of a loss the text is red.
You can combine more cells, thus generating a customised report in a snap. Put the conditional texts in different cells and combine with the CONCATENATE function prompted by conditions for more control and flexibility.

NB: instead of cell references, I recommend to define a name for the cell, for easier reading. Cell D3 in this example could be named "Result" for instance (Insert/Name/Define...). Simply replace the cell reference by its defined name in above formula. [In the example D3=Result it reads =IF(Result "larger than" 0, ..etc]. This also works in the conditional formatting formulas.

Patrick:

Just a thought. You can create yr custom shading (5% grey for instance). Or use different shadings for instance for totals and subtotals respectively. Or use borders with different weight. Or different font colours. Or ...

Another suggestion for Conditional Formatting usage. This is a application of Henk's #5.

Many spreadsheets do complicated slicing / dicing calculations that need to balanced to the expected outcome (a checksum). To detect imbalanced checksums quickly, I use conditional formatting with Stop Light colours i.e. Red = Problem. Green = Go.

For Example. Suppose the sum of a bunch of calculations is in A10, and a checksum in b10. My conditional Formatting would compare to the checksum.
=$a$10=$b$10 (Format Green fill pattern)

If the data needs to be printed into a presentation, add the checksum & special formats off to the side. Combine it with a formula in C10 such as: =If(a10=b10,"OK","imbalance") and Conditional formatting on C10 that flicks it from Green to Red when there's a problem:
Condition 1
=C$10="OK" (format Green fill pattern)
Condition 2
=C$10"OK" (format Red fill pattern)

There is one quirk of this technique. Excel calculates to very precise levels. I think its about 15-16 significant digits. Each slice & dice contains a fraction, and occasionally after lots of slices, Excel will round an answer up or down. I'm not doing quantum particle physics, so rounding to the nearest $1 is OK. The trouble occurs when the tally comes back as 10.000000001 = 10.0. This evaluates to FALSE, which triggers the RED. However, if you don't care about the .000000001 then you're wasting time searching for a fake problem.

Solution: change the conditional formatting formula to:
=round($a$10,0)=round($b$10,0) --- advantage, you can adjust the level of precision.
Alternatively:
=int($a$10)=int($b$10) --- will only evaluate to nearest integer

Patrick prefers three rows white to one row shaded, I prefer three rows white to three rows shaded, which I achieve by

=MOD(INT(ROW()/3),2)

If you want to experiment, try

=MOD(INT(ROW()/$A$1),2)

and in cell A1 put "3", or "1" if you prefer the original style, "0" to turn the striping off, etc.

I also prefer to use a custom grey, luminosity 246, instead of the 25% grey (luminosity 192) available in the defaults which is far too dark. 246 is about the shading in Lotus Notes databases that use striping as a display aid.

Chris:
Custom number formats indeed are not straight-forward. I only recently learned how to give the Value Axis a $-format, the $ showing ONLY at the top value. The trick is to put a condition between squared brackets in the Custom Format option (in this particular case use for instance (under Format/Cell/[Number] /Custom/) ["smaller than"99]#,##0;["larger than"99]$#,##0. (This example assumes a scale from 0-1000 dollar with a thousand separator (a comma)). You may like to present us a separate blog here!

But this is not what I intended with a conditional chart. Maybe an example here is illustrative. Suppose you want to show a bar chart with black bars when the corresponding number is positive, and red when negative. The trick is making TWO series that are complementary. Assuming the values to show in the bar chart are in cells B2 and lower, put in c2 =IF(B2 "larger than" 0, B2, NA()) and in d2 =IF(B2 "equal as or smaller than" 0, B2, NA()). Now either c2 or d2 shows the value in B2, the other one on the corrsponding row shows #NA. Copy the formulas down and u get in the c-column and d-column always a value and #NA pair. Plot the two series, c formatted as black, d as red. Because #NA is ignored by Excel you will get a mixture of black and red bars, the colour being "conditional" on the value (pos; zero/neg).

Possibilities for this concept are endless. E.g., in an XY scatter chart points of attention (e.g. the maximum value) can get a different marker (shape/colour) to let it stand out. Jon Peltier's website has some inspirational examples: e.g., http://peltiertech.com/Excel/Charts/FormatMinMax.html

Ooops. A correction to my earlier post: Condition 2 should read:
=c10 (greater than or less than) "OK"
(use the greater than symbols instead of words).

Another idea for colour coding data lists. This is an extension of the conditional formatting rows idea. Colour code your columns:

For groups of 4 (Quarters)
=MOD(ROUNDUP((COLUMN($A$1)-1-COLUMN())/4,0),2) (Format to Gray)
For groups of 12 (Months)
=MOD(ROUNDUP((COLUMN($A$1)-1-COLUMN())/4,0),2) (Format to Gray)

I cannot get any of these to work. Each time I try some of these in the condition field, Excel places double quotes around my expression. Why is this?

Chris,
I know this is not exactly on topic (related but not on topic) but your search seems to be broken...
Is there a way to store an entire custom pallette for the workbook in VBA?

Is there any way to apply the concept of conditional formatting to graphs. For example, in a bar graph or column chart, how do I make every bar that goes negative red?

Does anyone know of a way to increase the number of conditional formats above 3. We need to set 11 formats for each cell. Any suggestions

Brent -

<a href="http://peltiertech.com/Excel/Charts/ConditionalChart1.html" rel="nofollow">Conditional Charting:
http://peltiertech.com/Excel/Charts/ConditionalChart1.html</a>

In our department we have a Job Log in Excel. We want to be able to make every row that has "MFG" in it to be a certain color. Our row "$I" tells us if it is "MFG" or "Quote." We only want the "MFG" row to be colored differnt.

Does anyone know how to stop this formula cell :

=VLOOKUP(Formula!M10,Formula!M11:N12,2,FALSE)

from returning #N/A when no data is present. I tried conditional formating, but does not work for the #N/A description being equal to.

The worksheet and formula work great, this is more of a presentation eye sore.

Thanks for your time,

Rob

Hi Chris,

When I enter this formula it states there is an error in the formula? Should I insert the entire string as you shown above?

Thanks!

Hi Chris,

I tried

=IF(ISERROR(VLOOKUP(Formula!M10,Formula!M11:N12,2,FALSE)),0,VLOOKUP(Formula!M10,Formula!M11:N12,2,FALSE))

And it works, does this make sense to you? using the zero I mean!

It returns a zero instead of #N/A.

Please let me know and thanks for your help!

I am trying to look up the largest 3 numbers in a column then return a number in the same row. The problem I am having is if the numbers are the same it just returns the first and will not move onto the next number even if it is the same. Below is the formula I am trying to builf off.

=VLOOKUP(LARGE(J1:J12,1),J1:L12,3,FALSE)

=VLOOKUP(LARGE(J1:J12,2),J1:L12,3,FALSE)

Thanks for your time!

Chris,

Just so you completely understand as I cannot get this to work and I really want to make sure if this is possible. Please see below:

600 1
200 2
400 3
600 4

In the example above I would expect to get the results - 1, 4, 3 . as the largest is 600 and the next would be 600 also then 400 while each would return the next row numbers.

Please let me know if this is possible and/or if it makes sense and again thanks so much for your time and help.

great reference;
i know you covered it above -- however is there no way to create conditional formating with formulas that use relative references and when copied update those references to the current cell?

i have hundreds of fields i would like to change format on based on a silly calculation. however i just cant figure out any to copy the formula to other cells and automagically update the references in the formula.

editing those by hand seems daunting...

Dave, I hear ya bro! Good luck with that. I hope this was helpful :-)

I would like to use conditional formatting that highlights a cell if it is a multiple of 10, i.e., cell turns red when number displayed in cell is 20,30,40 but not 21, 22, 33 etc

Please help!

Thanks

Hi,

Is it possible to create a formula in conditional formatting that says: if this cell equals any other cell in this colum AND if the cell that it eqauls has a "Y" in the cell next to it, then highlight red.

Please help

Thanks

Hi,

I was just wondering if it was possible to use conditional formatting (or another formula) to highlight the cell if the value has changed, without having to go in and change the formula everytime a value changes.

Thanks

Is it possible to use conditional formatting to affect the whole row (not just one cell) if a particular cell contains a certain value?

For example, if one column contains the word "ambulance", I can get that particular cell to be highlighted. Can I somehow make the whole row be highlighted automatically?

I tried applying conditional formatting for the row by selecting the #2 for another example (highlighted the whole 2nd row), but only the cell that contained the word ambulance was highlighted, not the rest of the row.

Thanks!

I have a table that shows item numbers in one column and descriptions in a second column. I want to search within the description column and highlight rows that contain a certain number within the description. My issue is that the description column also contains other information and I cannot seem to figure out how conditional formatting might search the data for just the bit I want in this case. The issue being that the number within the description will be the only thing these item descriptions have in common and I cannot therefore set it to search for an entire description.

Suggestions would be greatly appreciated.

Thanks,
Andi

I am trying to format some cells based on the values that are returned from a formula. The formula is =IF(MONTH($A$1)=MONTH(SUM($A$1,COLUMN()-2)),SUM($A$1,COLUMN()-2), "") and it basically returns each date for the month you enter into A1. What I am trying to do is to make every cell that contains a weekend value (I have formatted the cell to read the day of the week) to turn grey and all other week days to stay unformatted. I just can't seem to work this one out. Any help would be much appreciated. andy

Just wanted to say that your conditional formatting page was a great find for me. Thanks for taking the time to create it.

in order to make data entry of some nums simpler, i have a list of policy numbers that begin with 10, however, the series begins with 0001234567 etc and keeps moving till such time that the numbers look like 1234567890.
can i use an if and concatenate in such a way that if there are 7 digits then 3 zeroes added to the list and if 8 digits exist then 2 zeroes are added!

rinx

Rinx, try using a padding function like LPAD (I think that's the correct function) to add the extra zeroes to the left. I've never used it in Excel, but in other languages where I've used it I think it will do what you need done. You can just specify the length, like 10 characters, and it will add the appropriate number of zeroes.

Also, a cool way to make a quick maze generator in Excel is to use the formula =randbetween(0,1) to a set of cells and set conditional formatting for those cells to top border for 0, left border for 1. With just a few hand placed walls you can complete the maze and you have a simple binary tree maze generator.

Great use of conditional formatting. Here is a puzzle for you: I use the Autofilter functions quite a bit to look at subsets of tables. Because this just hides rows and does not change the row number, the nicely alternating row shading turns into unpredictable clumps of color.

Any ideas on shading alternating rows of a filtered table?

I like the conditional formatting page, however I need to be able to highlight any occurance of the word Community. For example I might have Community Kids, Community Adults, and Church Community and any time the workd Community is in a cell, I want it highlighted. Any easy solution?

Thanks!

Rita, someone may have a more elegant looking formula, but I see that

<code>=NOT(ISERROR(SEARCH("community",A1)))</code>

works to test for the word "community" in a cell, where A1 is the cell in question. Use FIND instead of SEARCH if it has to be a capital C.

Doug, (Comment #37)

An intriguing problem.

If you use add a formula column with the following formula =isodd(Subtotal(3,$a2:a2)) it produces a TRUE FALSE that alternates each row and changes when AutoFilter displays or hides the rows.

You can then direct the Conditional formatting to that column, and format away.

Of course, a whole column of TRUE / FALSE is distracting so hide it.

Oh, and you have to pick a column in the subtotal function that always has a field in it. That shouldn't be an issue if your datablock is prepped for use with AutoFilter.
Worst case is you could add a fake data column to create your consistent counter.

Doug,

Better solution.

No extra column. But you still need a column in your data that has no blanks.
Conditional Formatting: Formula Is
=mod(subtotal(3,$a$2:$a2),2)=0
+ your preferred formatting techniques.

for every fifth row, the formula would change to
=mod(subtotal(3,$a$2:$a2),5)=0

Cheers

Lol....I see that my attempt to "type" in my format appearance didn't work too well. :)

Ahh well, I can easily send the file to any interested. :)

-Dan

Is there a awy to "Add" more than "3" color options to a 'cell value' conditional formating?
For example;
4-5: Green
3-4: White
2-3: yellow
<2 : Red

Rinx

Did you get an answer to your question regarding concatenate 3 zeros if there are 7 digits etc...?

I'm trying to do the same so wondered if you could let me know how you got on?
Cheers, Anita

I was working on a formula in my budget.

Right now, all deductions and deposits are entered in the same column (column B). With the current balance in the account in column c.

I differentiate between what I spend the money on by highlighting the cell in column b a specific color or shade.

What I want to do in column E is keep track of money spent per color. Just one color specifically (gray-25% - index 15).

Is it possible to have a conditional format in an IF formula?

I was thinking something like:

IF(B721=(interior.colorindex=15),SUM(E720+B721),E720)

I tried it but no cigar... am I close at all?

Is it even possible?

Thanks

James

Rinx and Anita, the answer to your question depends on whether you expect these to continue to be numbers, and simply wish to make them look as if they have eight characters, or whether you expect them to be text strings and actually have eight characters.

If the former, the formatting is simple: custom number format

00000000

will force all numbers entered into the column to appear to be eight digit integers, with leading zeroes if necessary.

00000000;"error - negative numbers not allowed";"not a policy number"

will provide some error checking for non-numeric policy numbers and policy numbers incorrectly given a leading hyphen.

[>99999999]"error - too many digits";[<=99999999]00000000;"error - negative numbers not allowed";"not a policy number"

will also error check for too many digits.

If the policy is to be an eight digit text string, I can think of no in-cell auto-completion that will give you what you want, but another cell can be populated with the formula

=REPT("0",8-LEN(A1))&A1

which will add leading zeroes to the string in A1 to make up the difference.

If you don't mind being forced to add the leading zeroes by a nagging error message, look up "Data Validation" in the manual.

Alternatively, various formulas based on text or number length can be added into the Conditional formatting dialogue to highlight policy numbers that are incorrect. Again, look up conditional formatting, and functions like LEN() in the manual for details, and read the main Juice article above for ideas.

I'm trying to work the top example (colouring based on percentiles)into a sheet, but some of the formulae I'm using to create the data will be returning zeros, or just "" to be more accurate, which is throwing off the percentile function. Is there any way to get the percentile function to ignore cells that are blank?

Many thanks.

Not to worry, I worked out what I'd done wrong. I'd forgotten to use $ to lock the named cells in the formula. When I copied the formula to all the cells I needed it in, it updated the named cells to include ones outside of the correct area, which threw everything out.

This article was great but, having applied it, I've got a situation where the conditional formatting works and fails at the same time! It's set to highlight a cell value > 0 green, < 0 red, and = 0 white - so that it 'disappears'. The cell entry itself is an IF function which checks for the presence of data in a particular cell and calculates one of several results if present. If there is data in the referenced cell and the calculation = 0, the formatting works - the 0 is white and is not visible. However, if there is no data in the referenced cell, a green 0 appears! I've checked with 'Add watch' and in both circumstances the cell value is given as 0 - but one goes white, the other green! Any suggestions would be greatly appreciated. Thanks.

To suppress the display of zero, use custom number format instead of conditional formatting.

Alternatively, rewrite your IF() statements so they handle the difference between evaluating to zero because of the referenced cell value, or evaluating to zero because the referenced cell is empty.

Thanks for this Derek. I'd used custom date formats before but not numbers and didn't know this could be done. When I first tried it using Excel Help, the 'Specifying Conditions' section enabled me to make it work for two conditions/colours but every time I tried 3, it wouldn't let me, as in ([Blue][>0]General;[Red][<0]General;[White][=0]General. I did another web search and on David McRitchie's MVP site I found a format I adapted which did work with three conditions - [Blue][>0]General;[Red][<0]General;[Color2]General. Obviously I can see the difference but don't know why it works while the other doesn't, but not complaining too much as it does work! Problem solved - many thanks. Colin

Colin, you don't need white, or color formatting in the number format (although it is sometimes a good way to extend the number of formats, in Excel versions before 2007 where there are only three conditional formats available).

The usual number format, without conditions, goes

positive;negative;zero

If you have three formats with no conditions in square brackets, it assumes the first is for numbers greater than zero, the second is for numbers less than zero, and the third is for numbers exactly equal to zero.

Also, if you have a blank after the second semicolon, it won't show a number at all for zero, even if the background isn't white (your formatting the font to white will only work on white backgrounds.

So if you want blue for positive, red for negative and invisible for zero, I recommend this format

General;[Red]-General;

then format the basic cell with blue. And now you still have all three conditional formats available for use even in Excel versions before 2007.

Nice one! Many thanks.

darrell (<a href="http://www.juiceanalytics.com/writing/2006/08/exploring-data-in-excel-with-conditional-formatting/#c41">comment #41</a>):

Thanks so much! It never occurred to me to use subtotal in the conditional formatting, even though I use it in other places. It worked perfectly. The formula I ended up using is:

=MOD(INT((SUBTOTAL(3,$a$2:$a2)-1)/3),2)

to get the triple striping <a href="http://www.juiceanalytics.com/writing/2006/08/exploring-data-in-excel-with-conditional-formatting/#c5">del c</a> described. You guys rule!

(And yes, it took me a over a month to remember to come back and check for a response.)

I'm looking to return a percentile with conditions....how do I do that? For example, I am looking for the 0.95 percentile of the letter 'A' in the given data set:

Part Value
A 10
B 9
C 11
B 32
C 20
A 16
A 32
B 17
C 25

I've setup a spreadsheet with alternating shaded rows with conditional formatting of:
=MOD(ROW(),2)=1

I also want to format this column with red font but when I do only the rows that are not shaded appear in red font.

What is the easiest way I can get the entire row to have red font?

Brian, you appear to have accidentally specified a font format in your conditionally-formatted pattern. Go to the conditional format you set up, and in the bottom right hand of the font format box is a button you don't see in format dialogues, except in conditional format: the "Clear" button. Click that and the font format will clear so that the conditional format does not decide what colour the text should be, only what colour the cell pattern should be.

I need to automatically format the cells in a column to the number style which is named in the column title. There are 3 defined options: "dollar" ($###,0), "percentage" (0.0%) and "index" (0.0).

Using conditional formatting I can get changes in the cell properties for Font, Border and Patterns but not Number.

Any ideas please?

I have used conditiona format as the if the value between 10 t0 20 green works fine
however if give the range 0 to 10 it colors the complete row i have 0 as output and need
to highlight those cells

Please anyone help me out of the problem

Thank you very much for your helpful tips.
I need another help. How do I change the color of the cells in column B based on the value X of colum A7 with the following criterias:
1. Green if X < 110%
2. Yellow if 110% <= X <=130%
3. Red if X > 130%

I tried to use Conditional format using the following formulas:

1. fx=$A$7<110
2. fx="110 <=$A$7 and $A$7>=130"
3. fx=$A$7>130

It works for 1st and 3rd criteria, but it didn't work for 2nd criteria (Yellow).

Any help is appreciate.
Ly

Ly,

Condition 1: Cell value is less than 1.1 (format green)
Condition 2: Cell value is greater than 1.3 (format red)
Condition 3: Cell value is between 1.1 and 1.3 (format yellow)

Scott

I'm really having trouble with conditional formatting not ignoring null or zeros. I have a bunch of columns that have cells linked to a master sheet and I'm using the MIN / MAX to flag the MAX red and the MIN blue ... but many of the cells will have no data or the formula will just return $ - because I'm using the accounting format for the cell. How can I get it to ignore those so they don't turn blue but still show the lowest actual number by turning it blue ? Thank you so much in advance!

Dave,

Presuming you are using the max/min within conditional formatting (eg, Condition 1: Formula is =A1=MAX(A$1:A$20) etc) then you can resolve your problem by putting a condition in the link back to the master sheet, like =IF(+master!A1>0,+master!A1,""). Zeroes and blanks will solve as "" so will not attract the conditional formatting.
Scott

Conditional formatting looks great for my purposes. But to take it further would be to use intensity of colour related to the value. Ie the further below the mean a value is the deeper, or perhaps brighter the red, and the further above the mean the value is, the brighter the green. Does anyone know how to do this? I would like to generate a chart that shows shading related to the values.

Barton.

Barton,

Conditional formatting (in Excel 2003 at least) is restricted to three conditions. Hence why traffic lights (red, amber, green) is as far as you can go.

Charting gives you more flexibility. For instance, if you were to do a pie chart of the distribution of values around the mean (by proportion) you could have as many graduations as you like and manually set the colours as you have described.

Scott

I'm trying to create an IF statement for a cell that basically says:

If the cell = "N/A", then change the value to 0 (zero)

I'm pretty new to excel cell formatting so any info would help.

Thanks -- Jim

Jim:
You cannot change the contents of the cell directly. Conditionally, you can change the appearance of a cell, though. E.g. #N/A can be made invisible by conditional formatting (format it to "white color" on certain conditions, here cell content is #N/A)
You can make an input matrix and an output matrix to show in the output the "0" on the conditions you want [IF (inputCell=NA(),0,InputCell)], but my guess is that is not what you seek.

How do have excel display zero (0) instead of negative numbers? Been working on this forever. Thanks in advance!

BK

Chris, other way around: 0;"0";0

The Excel custom format, er, format is "positive format, semicolon, negative format, semicolon, zero format"

Your way is more logical.

column "a" 1-100, column "b" name1 - name100. how can i put 10 numbers in column c which will look for and pick up the number in column "a" then give me what i'm after, the name next to it in column"b"

I have an issue with conditional formatting. I'm using it with three values, more values would be be great of course, and any of the values then changes the text box background color to say blue, green and red. How could I extend this so that while a certain value is given the whole row, not only the cell, would obtain the color defined in conditional formatting?
Looking forward to an easy solution for this one. Thx.

need to apply a format to a cell only if this cell IS NOT empty, AND a second cell IS NOT cell is empty but a third cell IS empty.

I have a spreadsheet that is 8col x 50+rows. I have added pretty complicated conditional formatting to most of the rows. Now I want to add another conditional format to the whole sheet, but when I select the whole sheet and click 'conditional formatting' only one conditional format shows up. Therefore when I click okay this condition erases all the individual row conditions I've added before. Is there an easy way to add an overall condition to existing individual conditional formatting. Also, in some of the rows I have all three conditions used already. Will this require using VBA, as noted in #13 - Chris, and if so, how would you go about doing this?

Ok, here is one for you.

How do you change the interior color of a cell based on if the value in a cell is different than the one above it.

If the values were:

1
1
1
2
3
3
4
5

The 2, first 3, 4 and 5 would be highlighted and the other items would be left normal color.

Is this possible?

Matt>
Yes, set your conditional format in cell A2 to Cell Value not equal to "A1" where A1 is the first cell. Copy and paste special down - formats all the way down your column.

Thanks for the custom number format help. It worked like a charm. Cheerz all!

BK

Please help.
I have a cell that I paste text into, that has it's own formatting, that I copy from an inrtranet site.
The target cell has a yellow background.When I paste the text, which has a blue background, or white background, the yellow cell goes white/transparent, but the text is fine. I just cant figure out how to not change the background.
I've looked into conditional formatting.
But can't find any solution.
Is there a way to ignore any formatting, and just accept the text.

Regards

Shaun,

>Edit >Paste special >Values

This works when I do this, but the problem is that I'm not the only one using this doc, it will be used by others too. I know to use >Edit >Paste special >Values
but other people just copy and paste.
Other solution:
I've changed the style formatting, which seems to work fine for the boarder, and the background, but it pastes the text as bold.
The text has HTML formatting.
Now another problem has arisen. See next post.

Problem: When I enter all these values, it stops the formula from working, and if I make any new or change any other formula, it doesn't do anymore calculations. The wizard result is OK on all the formulas.

I've restarted the PC aswell, as I thought it was a module lockup or something.

I have found posts on other sites with the same problem, but no solutions.

Please help?

Concatonation: I use three formulas to display a result.
The first one:

=CONCATENATE(E5,": ",E9," [CAT]",E7,"[ISP]",E8,"[TASK]",E10,"[CUST]",E11,"[REPORTEE]",E12,"[REP-SEOB]",E13,"-",E14," AHW=",G13," AHH=",G14,"[ADDR]",E15,"[CONTACT]",E16,"[SITE-SEOB]",E17,"-",E18," AHW=")

The second one:

=CONCATENATE(G17," AHH=",G18," [Service Name]",E19,"[BS]",E20,"[BS-ID]",E21,"[MAC]",E22,"[LAT&LONG]",E23,"[SYMPTOM]",E24,"[TEST-RESULT]",E25,"[TESTER]",E26," ",E27)

Then a third to join the two results and display in another cell.

=IF(K37=TRUE,"",IF(K38=TRUE,"",(CONCATENATE(E60,E61))))

I use the IF portion of the third for some other funtions, which have to be completed for the result to become visible.

Hi.
Sorted out the CONCATENATE problem. Had to retype it from scratch, and decided to use the "&" function.

1. I still have a problem with the text being pasted to a cell.
I have to leave the cell unlocked, so that the new text is pasted to the cell. The background and border I can now keep, as I'm using styles on that cell, but it still locks the cell. I haven't been able to stop it.

2. I also have a macro run to clear all the fields in the spreadsheet, ready for new info. If the above text in 1. is pasted to cell E20, it is locked, and I cannot run the macro on a locked cell. The sheet has to be locked, so that things are not messed up.

I can't seem to find a VBA to include in my macro, to unlock the sheet and unlock that cell before clearing the contents and locking the sheet again.
Is there a way to do this, I am very new to VBA at this time. About two weeks trying to figure it out.

The books I have are just not helping.

3. I am considdering doing the whole project in forms, instead of using the excel spreadsheet. (If I do create it in Excel VBA, will I be able to import it into VBA and compile it as a standalone at a later date?)

Sorry this is so long.
Shaun

Thanks guys

I was able to sort the problems out after much searching the net and modifying the code.
Now that it works, I'm going to start developing the form to do everything that the spreadsheet did, including a whole lot more.
I'm likely to need some help, as I want to finally compile it and use it as a standalone app.

Regards

I have a User form.

1. How do I make TextBox's, ComboBox'x and CheckBoxes grey'd out and unavailable when a specific CheckBox is checked.

2. How do I enable right_click&copy/cut/paste in the same userform.

3. I need to have a user insert user details, then click save/update, and the details save to a file in the users folder
C:\Documents and Settings\Userxxx\My Documents\Userform\User.txt. The form when opened must check for the file and populate the form with the details.
The save button must be disabled if details are available, however if the clear button is pressed, the save/update button is enabled till pressed. (Various users use the PC's, so it must be saved in their own folder)

I have to output the values in the form to the clipboard, once all the required values are inserted, as there are various fields that will change, depending on the situation.
I will work on that at a later point, but the above, I cannot seem to find any info on the web.

Sorry this is so long.

Problem with formatting. I've got a master spreadsheet with very detailed formatting that summarizes data from many different departments. Each month, I received an "input" spreadsheet from each department.

My master spreadsheet uses links to update the master... however, we're not implementing a rule where each department can "color code" their input based on different characteristics. Their color codes are infrequent and are manual. However, when I update the master, I need to bring across the color formatting, which my link formulas do not do.

Does anyone know of a way to include something in a formula that says to grab both the number and the format? Basically I want a formular that does what paste special formats does... or better... a formula that does paste special format and paste special value in one step.

Any ideas????
Thanks in advance for your help!

Good evening.

Trying to get a cell to shade to grey and lock if a non adjacent cell has a value of "steady".

Thanks for any help.

Sam

Hello!

Can someone explain how I manage 4 variables in conditional formatting please? I need to edit 4 cells in row if the content of the first cell is equal to "complete", "red", "amber" or "green".

So for example:-

if A1 equals "complete" then highlight cells A1:A4 in blue

if A1 equals "red" then highlight cells A1:A4 in red

And so on and so on...! This is for a weekly report I have to produce, at the moment I use this formula in conditional formatting to achieve what the above:-

=$D74="Red"

This would be fine, but I have four variables and not three!! So if I could for example say if not red, amber, green then highlight cell as blue as it must be complete etc etc...

Any help most appreciated :)

All the best,
Jamie

Hi-
I need to highlight an entire row if a certain UPC appears on a rank report. But I don't know how to conditionally format for a range of numbers. For example if the # in C9 is between 00115090000 & 00115099999 then highlight the entire row. Is this possible?

Thank you,
Clare

Hi all-

I have a spread sheet that I would like to reference a total based on two numbers (N2&N3), one (N2) the represents a series of rows (B8-B48) and one (N3) that represents a series of columns (C7-Z7). Conditional formating does not seem to meet my needs. Can anyone give any help?

Thanks in advance...

BK

I'm busy setting up a userform, and need to have a user enter their details in the userform, to be saved to a file in the user's My Documents folder.
1. Program starts, and checks if the folder and file exists.
If yes, retrieve user info from file.
If no, create folder ..\x\. user must insert details and save the file.

I'm considdering a posible link to the Exchange server to retrieve the user details.
But more on this later, or any suggestions on how to do this?

Sofar I can retrieve the userID logged on to the PC.

Anyone with some help?

How do you copy Conditional Formating to other cells in the following condition without having to edit the formula for each and every cell.:
Example:
Cell B1 turns RED if Cell A1=1

Formula FORMAT(RED) Applies to
$A$1=1 Fill Red $B$1

When Copying the Formula Down for cell B2 to turn Red if A2=1 B2 Links again to A1 NOT to A2 as required.

Copies Down As
Formula FORMAT(RED) Applies to
$A$1=1 Fill Red $B$2

Any suggestions on how to Copy Down the Formating without having to Conditionally Format each and every cell?

Thanks

Answer for those of you with this problem.

I figured it out after a cup of coffee & Lunch

Change the formula as follows
$A$1=1
now becomes
A1=1

By removing the dollar Sign $ the referance is no longer absolute and automatically changes with each cell.

Hope it helps someone.
Cheers

Answer: (Repost)

Change the Formula from
$A$1=1 to
A1=1

Removing the Dollar Sign($) stops Excel from using the value as an Absolute and it now changes to the relative cell as intended.

(Just needed a cup of coffee)
Hope this helps someone
Cheers

HELP ME.

I have a contacts list. I am going through it confirming all the peoples details like address and phone number. As I have spoken to each contact and their details are correct I want the row with all the details on it to turn green.

I thought if I put a C for complete or P for pending in the correct cell in coloum 1 I could get that specific row to turn green or yellow?

How do I do this because obviously each cell contains something completely diffferent.

HELP :0)

I want to format a column with the following-
=L1>0 and =Z1=yes
If true fill A1 cell green.
I can do each of the above seperatly but I can not make it work with the AND.

Who can help? Everytime when open excel,the row height for whole excel is over 20, not normal 12.75. Try to uninstall office and reinstall. Also try to remove office excel from regedit file. But still keep the same.
Where is the wrong? Hope to get your solution. Thank you!

I am trying to do the same as Felicity Green (comment 97), to make a whole row change color according to the text in a single column on that row. Felicity (or anyone) - have you found a solution to this yet?

Hey guys,
I'm stuck

I am doing an office pool, each person has 6 numbers selected, I want to make those numbers turn green if a number is drawn.

For example my numbers are 1,7,11,22,25,47

the numbers drawn are 1,9,11,26,38,47

I'd want all numbers that match to change color?

any suggestions? I figure it would be a IF formula, but I can't firgure out the logics

Tim said above: "I am trying to do the same as Felicity Green (comment 97), to make a whole row change color according to the text in a single column on that row. Felicity (or anyone) - have you found a solution to this yet?"

Me too! Has anyone figured out how to do this?

Tim and Felcity: I did get it to work where Excel conditionally formatted a whole row based on the text in one cell.

This is the formula I used:
=$B4="FS"

I chose the entire spreadsheet as my range. It looks like this:
=$B$4:$T$58

Looks like the key is in where to put the dollar signs.

I don't know WHY it works (why do you need to input just the 'b' and not the number), but it is working perfectly.

I am trying to create additional "conditional formats" in Excel 2003 and cannot get the visual basic codes to recognise.

Using:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True

Dim icolor As Integer

If Not Intersect(Target, Range("A1:AU69")) Is Nothing Then
Select Case Target
Case 75.1 To 90
icolor = 35
Case 90.1 To 100
icolor = 4
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

If anyone has any bright ideas would be good to hear from you.

Cheers

Can anyone tell me how to combine the last two formatting types above? i.e. Say I want the line formatting as in the last example, but I also want Garciaparra, Thome, and Howard rows all shaded in green, Dunn in white, and back to green for Hafner and Giambi, and so on.

Thanks, Dan

Lisa West, try doing it like this:

Const WS_RANGE As String = "A1:AU69"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case Is >= 75.1: .Interior.ColorIndex = 35
Case Is >= 90.1: .Interior.ColorIndex = 4

End Select
End With
End If
ws_exit:
Application.EnableEvents = True

The dollar sign is used before the row or column to keep it fixed when the formula is used for more than one cell. For example =$A$1 will always result in the value in A1. Using =A1 it would change to =B1 in the next column or =A2 in the next row and so on. So the reason why =$B4="FS" works is because for all of the cells the formula will look at column B in the current row.