I've set up a condition where a cell changes colour if it's equal to 0 (red) or to 5 (blue), however when there is no value, I'd like the cell to have no colour. Is there a way to do this? Right now, the cell defaults to red even when there is no value.

Yes, simply use the following code in your conditional formatting:

=$B5=""

There quotation marks should be empty.

Scott

Is there a way to create more than 3 conditions?

RE: Is there a way to create more than 3 conditions?

Not within the same cell.

How would you compare two list

Is it possible to condition format in a cell to query another cell that has a result in it that is generated by a formula.

ie. Cell A1 = 2
A2 = 4
A3 = (Sum of A1+A2)=6
A4 = (Condition Format if A3 = 6 then colour red) if not green.

I'm trying to create an item list with items on the left side and vendor names on the top row, each one associated with a color, where we can type in the item prices in the cells and excell will automatically highlight the lowest price on the row for an item. I've looked at all your examples and tried to figure it out myself but either it will only reference one row or it will highlight the entire column. Any ideas or formulas that you can apply to the entire sheet or would I have to create a formula for every cell?

Can the conditional be based on color? If the cell / text is Red, then...??

RE: Can the conditional be based on color? If the cell / text is Red, then...??

No.

How do you base a condition on the value of another cell. I want to highlight the name of a tenant in column b, only if there is no entry date in column c for them. I can't seem to find the right way to write the formula in the conditional format box.

Is it possible to check a cell containing a 2 or 3 character string against a list of 18 cells (also 2 or 3 character)? To be simpler:

If Cell A = "AB" and "AB" does NOT appear in that list, color the cell red, otherwise green?

Conditional formatting based upon testing whether a cell value appears elsewhere in a list is relatively simple. Use the countif function, so conditional format where
formula is = countif(range name of list, cell reference of current cell)=0 and set the colour you want it to be if the cell value doesn't appear in the list.

Is it possible to condition a cell to display a word (yes or no) depending on a separate cell?

> Posted by: Carol
> July 4, 2003 11:19 AM
>
> Is it possible to condition a cell to display a > word (yes or no) depending on a separate cell?

Yep, ie
=IF(B1>A1,"Yes","No")

Replace "B1>A1" with the condition you want

How would I make the formatting conditional on whether or not the cell contains a formula or value?

I’m using Conditional Formatting to turn various cells RED, GREEN and YELLOW, so I don’t want to have to manually reformat or make a separate copy and reformat every time I have new data. Any ideas how to turn off the formatting for people without color printers, but so they can still visually see the difference between RED, GREEN and YELLOW on paper?

I have a worksheet with about 5000 userid and in another worksheet I have some userids that would be present on the original list. My question is:

I want to be able to lookup from the main worksheet to the other worksheet and change the color of a field is the field matches a field on the second worksheet.

How would I do this?

hi
I am trying to create a grade based on the range of values that i have.It is something like
90 80 How would i do this

How do I show different colors depending on date month's? For example, if I want all dates in a column to show red if it's month is before today's month, yellow if it's month is this month's and green if it's month is the following month, how would you do this?

Thanks - Victor

I need to load a csv excel file into oracle. One of the column
on the file shows data in the format of 5.00+01. How do I format this column to show the right data? The column should be of format text.

Is it possible to: Apply conditional formatting based on the value of an embedded check box? i.e. If Check Box value = true, then Cell shading for (a2:$s2) = "a certain color."

Posted by: Wesley (xxx.207.43.130)
September 5, 2003 06:17 PM

Is it possible to: Apply conditional formatting based on the value of an embedded check box? i.e. If Check Box value = true, then Cell shading for (a2:$s2) = "a certain color."
---------------------------------------------------------

I have done what you are asking, but I don't have the spreadsheet handy. I seem to remember linking the checkbox to the cell that it is on top of and then using conditional formatting in another box to check the linked box for true or false. The example at the top of this page shows you how to go from there.

I have a table of names with a code in ( ) following the name. I want to format the cell color and text color based on the code not the name. Ex. John Smith(CAR), Jim Brown(IND), Jason Douglas(TEN). If the above names are in seperate cells A1,A2 and A3 and I want to format the cells that have code IND and TEN how do I accomplish that?

How do you enter a conditional format formula with an OR statement?

Like if I want to have a red turn red if the letter is n or q?

This formula will not work.

=OR("n","q")

HELP! Urgent.

I need to have a cell display a name when another cell has a specific value in it. I have tried to put =IF(A1>B1,"Yes","No")
but it don't work. (Excel 2002 SP2 on XP Pro)

Can someone give me a working solution to this urgent? Please.

If A1 is bigger than B1 print in D1 "Yes"

George, you are exactly correct in what you posted above. Just make sure the IF statement is in cell D1, and you're using the right cell #'s (A1,B1,etc are usually column headings) :-)

Help!

I am having exactly the same problem as Kirk. I have tried using the OR statement in different ways with conditional formatting but to no avail. It seems Excel has a limitation in this way.

If anyone knows a way around this I would be very grateful. Alternatively, if there is a way to get around Excel's strange limitation of only 3 conditional formats, this would be even more useful.

Hi,

I am trying to find a way of using conditional formatting to ascertain whether any of the cells that I'm linking to a target sheet is over a certain limit. I would like the target cell to alert me to the fact, using perpaps a colour. Could I use an If statement for this. Please help.

how can i copy a conditional fomat that i have applied to one set of cells to another..
Paste Format doesn't work..
thanks

Why can't I select conditional fomatting from the menu? It's the only option greyed out!
Thanks

I want to apply the same conditional formatting to an entire column. Each cell might have a different date in it. If any cell in that range is 2 days less than, more than, or equal to (today) then I want that cell to turn a certain color. How do I do this to my whole column instead of setting up these conditions one cell at a time?

ex.
Cell 1 = 1/15/04 (this cell is equal to today (1/15/04) so I want it to turn red)
Cell 2 = 1/13/04 (this cell is 2 days less than today so I want it to turn green)
Cell 3 = 1/20/04 (this cell is greater than today so I want it to turn blue)

How do I do this?

I would like to change cell formating based on modification date. For example, a user updates a a field with a new deadline. That field text would display as red for 24 hours after the change is made. Totally impossible?

If I create a conditional statement that outputs "", will Access treat this cell as a blank cell when I import it, or will it treat the "" as an actual value of some sort?

Is it possible to condition format in a cell to query another cell that has a result in it that is generated by a formula.

ie. Cell A1 = 2
A2 = 4
A3 = (Sum of A1+A2)=6
A4 = (Condition Format if A3 = 6 then colour red) if not green.

I am trying to get Excel to turn a cell to the color I am thinking of when that cell is selected, but it always turns the cell pink! What am I doing wrong!?

Ok, that was silly.

Actually I want it to highlight the cell red based on a count of the cells contents on another worksheet. It won't read another worksheet from w/in the conditional format .. uh.. thingy.
So I made a column of the quantity counted and put a formula in the conditional format.. thingy.. to read that value and blush on command that way (>0). It takes the formula, but don't gimme no red! (punk!)

Here's the conditional format formula (can you even USE "if" statements in that?):

=if(O2>0,"","")

I didn't have anything to put in the true field. (lemme try just putting =O2>0 come to think of it)..

I am trying to apply a conditional format that would require cell to contain 17 characters,(numbers and letters)ie:
"N78654SR9876589JL",is this possible?

I am trying to set coniditons based on the following. If cell D12 value is between 46.99 and 49.99 than I want cell B15 to be yellow. If the vaule of cell D12 in not between this value than cell B15 should stay white. Is this possible?

>Andrew

Yes, use the len() command in formula mode on the Conditional format.

len(a1)=17

>Will

Once again, in formula mode

and(d12>46.99,d12<49.99)

that formula should be in cell B15, or whichever you want to change colors.

my question is almost the same as 19). i'm trying to get the cells to turn green 2 months before todays date, yellow 1 months before and red the day of and every day after.
i have tryed.
cell value is grater than or equal to ="Date" (formate cell red
cell value is between ="date-1" & ="date-32" (formate cell yellow)
cell value is between ="date-33" & ="date-64" (formate cell green)

In "Cell Value Is" mode:

Condition 1
greater than =TODAY()+59
Condition 2
between =TODAY()+1 and TODAY()+59
Condition 3
less than or equal to =TODAY()

Mind that conditions are examined starting from the first
and not progressing to the next unless the first is false.

I have it here so that on the 60'th day from today's date the cell will be green. On the 59'th day from todays date, the cell will be yellow.

Usarian M. Skiff

THANKYOU FOR THAT. IT HELP HEAPS

Hi

How do I get the cells on sheet1 to change colour and enter the current date when I enter a cross and the value 1 in a cells on sheet2.

Thanks!

Well, that depends entirely on the specifcs of what you want to do.

A generic answer would be as follows:

On sheet 1, select the cell that you want affected by the data on sheet2. At this point I will make up cell addresses for the sake of the demonstration.

in sheet1!A1 put:
=if('Sheet2!'B1="x1",TODAY(),"")
^^^^^^^^^
click on
the cell with the info,
don't type it out

Problems: TODAY() will ALWAYS show the current date, even when the date changes, it'll change with it. I suspect this is undesireable. You will probably need a VBA macro to accomplish what you want.

To change the cell color, really, just follow the steps Mr. Manning here put. click the cell, select conditional under FORMATTING at the top of your screen, and put in your specifics.

btw, has anyone else noticed that that formula thingy just doesn't seem to work the same way formulas do in the rest of Excel (or Open Office / Star Office)

Usarian

I have been using Conditional formatting in practically every.. probably literally every Report I make for a couple months now.. it's absurd how many uses you can find for it.. for example, I think my coolest (and simplest) use to date is this:

You know how much easier to read a printed list can be when every other line is colored or shaded differently? Well, I just made a Query summary report for the Sales department summarizing customer order status. I wanted to put a printable grey background to every other line, but I didn't want to have a page filled with grey lines for a one item order at the same time. I put a conditional format on every other line to turn the background grey if there was a non-blank cell in the quantity column.

Tricks:
The stupid thing kept putting qoutes around my empty quotes and messing it up, but when I fixed it, it stayed fixed (odd eh?)
I wrote the equation having several columns highlighted. When I clicked ok, it changed the column it looked in for my non-blank. I had to put the $ before the letter.

Usarian

I have a question.. Lets say I use conditional formatting to code colors to cell values based on unique percentages for each cell value. When I need to sort the cells in another order, is there any way for the formula (color code that is specific to a particular cell) be also sorted. So far, the formula remains in the cell and is not sorted along with that cell value.. Too confusing??

I have a column of cellS(B)that contain and X if I have paperwork for that person. After, a particular day and where an X has not been placed, I want the cell to turn yellow to make that more obvious but leave the others (one with X's) white. I have been trying to figure out how to do this but I am kind of failing.
I've tried using a seperate date cell as a reference to make this work but still nothing.

Anyone have a solution?

Shalini>

It sounds like you are sorting a column, but not having the conditional formatting applied to the entire column. Am I correct? If the same set of conditions will be applied to all cells in the column, try just drag copying from the cell with the conditional formatting rules in it the entire length of the column, then click the little box that appears in the lower right corner and select FORMATTING ONLY. (yes, you will overwrite all your data initially, but it will come back. you may want to save a working copy of your file before you try this though, just in case).

Sasha>

Does it have to wait until the end of the day?
You could just have them all yellow until the X is placed in them.

That would be the simplest solution.

If you absolutely need the dates tracked, it will be troublesome for you, but it is possible. Either put the date manually into the cell next to the X (or even stop using X's and replace the designation using the date), and include the cell containing the date in your formula. eg. =A3

Hope that helps. Let me know!

Usarian M. Skiff

=(F3>30,"yes","no") returns an error when F3=43 but =F3>30 does not when i just have it change the cell color to red. people have said this works in previous posts... but i can not get it to work. any ideas?

I have an array with conditional formatting done to it. I want to somehow copy the colours of the cells and paste them elsewhere and get rid of the values. I want to somehow "fix" the conditional fomatting highlights so I can move it elsewhere. IS this possible?
Thanks for the help.
Cheers,
Trev

What can I do to make a date flash to indicate it is approaching or has a past due status? I have used the conditional format function wherein the text/background color changes relative to the current date; I would like to add a little more functionality to raise awareness.

-Chris

Usarian,
Thanks for the help, your comment got me thinking and I came up with a solution.

I used this formula:

=AND($B$28>D$28, D5=0)

Names down the left column, months across the top. Where B28 is the current day [NOW()] and D[through N]28 are the monthly expiration dates across the bottom. The outcome is that the cells stay white until the expiration date is passed at which point the background format of the blank cells turns yellow.

Sasha-

Usarian,
Thank you so much for your response, I worked it out the way you said and it seemed to be the most optimal solution

Jason Barnes:

You cannot put text into a conditional format. It is a condition for a type of formatting of the cell (like the background, italics, bold, font, etc.)

Trevor:

You can copy Conditional Formatting the same way as any other formatting. I answered this exact question for Shalini about 2 posts up from your question. The trick is to get to the "Paste Special" menu item, and paste formats from there. When you drag copy or double-click fill a column (or row supposedly) it will overwrite your data, but when you click the little box at the bottom right of your fill (and it will ALWAYS be on your screen, you dont need to scroll around) you will get the special paste menu. Click Formats only and all your data magically reappears.

Chris:

Hate to disappoint, but it cant currently be done.

If you absolutely insist on finding a way, and you know a little programming, look around for a 3-rd party module (google search) that someone like you or me may have written. Mind you if you find one and use it, it will ONLY work on your computer (and who knows how it would print).
There used to be an ansi code that would cause blinking. (and ctrl-G used to make your computer beep.. there was an ansi code for that too), so I wouldn't doubt it al all if someone out there has found a way to make it happen.

Sasha and Shalini:

Thank you for telling me how things turned out!

Hey, I'm sure you already know this, but I've had a few issues with it before, so I'll mention it, but
NOW() returns a time/date, but TODAY() only returns a date. This can make a difference particularly in comparing data. For example if you compare using NOW() with a entry containing only a date, Excel may add a false time (usually 00:00:00) to make the comparison. That messed things up for me once. If you are only interested in comparing the current date, it can be good practice to use TODAY()

I just ran across a website that tells you how to write macros to make text blink and use as many different conditional formats as you need. Mind you this is a Visual Basic Macro, not a formula or feature directly of Excel, and you will have to edit the example quite a bit to suit your needs.

http://www.personal-computer-tutor.com/abc2/v18/chad18.htm

I need to put certain shapes like ® in different cells and this shape is equal 1 if I input any numbers of ® in any cells Excel
Possible to calculated automatically

I would suggest using len()

for example, any formula that needs to use the number calculated from the symbol would use:

LEN(A1)

instead of the cell A1

This command will return the number of characters in the cell. If you are using a mixture of different symbols to represent different numbers (eg. if you were going to use Roman Numerals) then this method would not be preferable, but if you are simply using a single symbol to represent 1 a number of times (like rating movies with stars), and there is no alphanumeric character in the cell (including a space), then this will work fine.

Let me know how it works out!

-Usarian

Need some info if anyone could help.
I know how to make a word or letter appear using conditional formatting such as =IF(A1>G1,"Yes"), however how would I do this if I had multiple cells I wanted to add such as B1, C1, D1, E1 etc etc. I still basically in Column G in cell 1 if anyone of the values in cells A1 thru F1>G1 I want the word Yes to appear. Any suggestions? Thank you for your time.
Chris

I would suggest using the MAX() command in there..

=IF(MAX(A1:F1)>G1,"Yes")

I'll have to look into that a bit more in a minute though

Usarian,
I tried what you said and though no success. Heres what Im working with. I have 6 columns that have have 155 cells in each. Within each Cell I have dates. In a seperate cell I have the current date that is updated daily. In a 7th column I have it titled "Inspections Due" and wanted the formula to have either Yes or No appear if the dates in the seperate cell are less than or equal to those in Columns 1 thru 6.
Now if I do a single cell seperatly it works, but for some reason once I go more than 3 cells using the MAX, "No" appears ....the formula I was using is this =IF(MAX(D6:J6)<=B3,"Yes","No"). Now in two of the cells the dates are in April however "No" appears in the formula cell when It should be Yes.

My request goes like this;
I have a spreadsheed where column A is main account number, and column B is item number. There can be 3 rows with account number 120, then 2 with 125, then 4 with 130, etc...
How can I have conditional formating do the following:
whenever there is a change in main account number in column A, the row color changes... just like getting those green stripes on print outs, except instead of changing every line, it would alternate every time main account number changes...

Thanks

I would like to set a conditional format to draw a cell red, yellow or green based on it being = to a number..this is easy..however, I would like the data entered in one cell but the color to change a seperate cell. So if I enter the number in cell A1 how can i get the color result to display in cell A2?

=IF(MAX(D6:J6)<=B3,"Yes","No")

is this really not working for you?

odd. well.. actually, I haven't tried it using dates.. dates are always wierd

ok, i tried it with the dates, it still works.. I dont know what to tell you. do you know how to use the evaluate feature?

That could tell you where the problem is. Just for those who dont know I'll spell it out.

What evaluate does is step through your formula and show you the result of each individual step. It's really cool, and very useful.

To use:

Make sure your cell is selected, then click TOOLS-->FORMULA AUDITING--> EVALUATE

keep clicking evaluate to move forward through the steps

J,

Well, conditional formatting cannot look at the formatting of other cells, unfortunately. You would need some cell value as a basis for comparison.

If you can add a column of data (perhaps hide it) to your worksheet, you could put the following formula in it, and format based on that column's contents in each row.

lets say you use column J as the flag for highlighting. J2 would contain this formula:

=IF(AND(A2<>A1,A2<>A3),IF(J1=1,0,1),IF(A2=A1,J1,IF(J1=1,0,1)))

and your conditional formatting would look for a 1 in J2 and format the color you want.

Is it possible to copy/paste only conditional formats? I don't want to loose any formulas or the standard cell format. I thought you could use copy - go to - special - then check conditional format - ok - paste. It doesn't seem to be working.

I answered this question a little bit higher up with more detail if you want to try to find it.

Yes, you can copy formats only. Use the paste option "Formats Only"

In regards to conditional formating, is there a way to specify a day when the cell data contains a date? All I'm trying to do is change the background of a column of cells to gray when the day is either Saturday or Sunday.

Yup. There is a way to turn the background grey on the weekend.

Go to the cell you want (I'll call it A1) and select conditional formatting, then select formula.
put this formula in the space

OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7)

and set your formatting. if you want the date to disappear in the weekend, the easiest way would be to change the text color and background color to the same color.

Hope that helps!
Usarian

IS IT POSSIBLE TO MERGE TWO CELLS BASED ON THE VALUE OF ANOTHER CELL? I WOULD THINK THIS IS SOME KIND OF CONDITIONAL FORMATTING PROBLEM BUT IT DOESN'T APPEAR TO BE ONE OF THE OPTIONS. IS THERE SOME CODE THAT MIGHT ENABLE ME TO DO THIS?

Is there a way to copy formatting without copying the conditions behind it... that is, to "fix" the formatting onto the cells so it will no longer change. For formula's, I can do this by using paste Values. Is there a similar way to make formatting permanant?

John

John, not really. you could make the same formatting by hand to the next cell and copy that formatting, but conditional formatting is intended to be dynamic.. it's pretty limited altogether.. i think we all hope MS expands it's capability in the future.

Mike, in your question about merging automatically, it can be done with vba. I can't go into detail right now, but check my site for the answer later:

www.freewebs.com/usarian/excel.htm

Is there a way for me to select every other cell using a formula? For example, I would like to color all even cells if there is a value in it, but I don't want to individually select 2000 records and color each one.. Can someone help with this?

I find this site very helpful.
I work a lot in Word and Excel.

Is there a way to type 123 in an excel cell and get it to show up as 1.23, without typing in 1.23?

Is it possible to create a formula or use conditional formatting to hide cells all together when the data equals zero? (have entire rows appear and disappear).

Can I compare two lists in separate WORKBOOKS!
How would I write the forumula to find the differences between lists in different workbook excel files? I was trying to use named ranges in each file. Any help greatly appreciated!
thx
Perplexed in Los Gatos

Help, anybody?!?! I've been spending way too much time on this and can't find a solution.

I designed a simple billing spreadsheet with 3 columns: DATE, DESCRIPTION and HOURS.

The DESCRIPTION cell contains text and is formatted to wrap. The text takes up anywhere from 1 - 4 lines.

After each entry I leave an empty spacer row.

I'm working on a backlog of bills, that are not in chronological order and therefore I need to sort by the DATE column.

When I sort by DATE, all the empty spacer rows disappear. Inserting them one by one after each entry is obviously too labor-intensive. I looked everywhere in Help files and online to find out how I can insert the empty spacer row back after each entry.

Adjusting the height of the rows doesn't work, because of the varying length of text in the DESCRIPTION column.

On another site I came across an idea with the Format Painter, but again, the DESCRIPTION column, with anywhere from 1 - 4 lines, gets in the way.

I'm at my wit's end.... Does anyone have a solution? It can work one of two ways: Either to prevent the empty spacer rows from disappearing in the first place when I sort, or how to insert them again in in one fell swoop.

I'd be grateful for any help.

I have a simple problem all I want to do is have every other row a different color I don't care what is in the rows. I know this is a simple problem I just can't remember how I did this in the past.

I like to set range B so that when A is ="Globa" for B to turn red or a pop-up messagge. is that possible.

Sorry I haven't answered lately. Many of you already have you questions answered in the above postings. If you take a moment and scan thru them you may find the answer you are looking for.

Crystal: your answer lies in a VB macro. I would write one for you, but I am running short on time. (sorry)

GARDY: Yes, what you wish is possible. Use the formula mode of conditional formatting and see directions above.

John Barnett: ..select the row, right click, format, pattern

Steve: Conditional formatting is not capable of comparing two workbooks or even two different sheets in the same workbook. You can use VBA macros to accomplish this however.

CSP: Intriguing thought. Without actually checking, I would suggest making a conditional format to turn the contents the same color as the background (eg white)

Wendell Oaks: I messed with this one for quite a while a few weeks ago. I could use this type of thing myself. I havent found a way to do it, but I found some custom (NOT conditional) formats that come close. none seem to be able to move a decimal in the end, but dashes and things seem to be placeable.

junior: I am experimenting with this sort of thing myself, but it will be a macro, not a formula. a formula can only process information IN cells. It cannot act outside of itself like a VB macro can.

There, I hope I got everyone. Let me know if you need more help.

Usarian M. Skiff

Usarian,

I am have created a spreadsheet and am trying to utilize the conditional formatting: I want the cell to be blanked out if it equals to the cell before it. (i.e. E2= 10 and E3=10 then E3 will be blank) I tried formatting each and every but it has become tedious. Is there some statement I can put in so I dont need to format each and every cell? Thank you in advance!

so you got the formatting to work properly, you just need it in every cell then?

If that's correct I answered this same question in detail twice above. Breifly, you can drag copy or double-click copy formats only. Copy your cell as you would a formula, the click the little blue box that appears at the bottom right of your screen attatched to the copied column, then click formats only

I have a list of names in column A, each with a different format (ie John is bold, Jane is size 12 font, Bob is red text, and Fred is grey background, etc...) Is there a way I can format the cells in column B so that if I type John in B4, it looks up "John" in column A and changes the formatting of B4 to match?

Hi all,
I wish to apply conditional formatting to a cell and highlight it bright yellow if the previous cell has been completed but the cell to be highlighted has not
So lets say the information that should have been entered into cell A1 was omiited and the user completed info into cell A2 as soon as the enter button was touched from A2 cell A1 is highlighted indicating info was omitted.
All help gratefully accepted

I am trying to figure out how to calculate the number of days between two dates in EXCEL 2000. Example; I enter any date into cell A1, and any other date into cell B1, and desire to see the resultant difference in the number of days in cell C1. Can someone help me? PLEASE! Any help most greatly appreciated.

Gerald-

yes, use a command called days360()

the syntax is:

=DAYS360(A1,B1)

it begins at the first date and ends at the last date. If the second date preceeds the first the number will be negative, which, of course, is easy to deal with if you need raw numbers.

There are a variety of counting methods you can use with the command as well. These you can get from the help file in Excel.

Usarian M. Skiff

Could someone help me with conditional formatting? Is it possible to format a cell to be displayed with white font whenever the cell displays #N/A or #VALUE!? Thanks

Hi,

I have a spreadsheet with a list of 10,000 dealers. About 200 of them are irregular and fall onto a single column of a second line. Does anyone have any suggestions as to what would be the best way to go about putting that column of the second line onto the last column of the first line?

Thanks

I really would like to know a way to have more than 3 conditional formats per cell. I am doing an aviation photography database and want each manufacturer to automatically be formatted differently. I want to be able to add more than jus Airbus Boeing and other. I want to be able to add formatting automatically to Beech, canadair, Avro, Antanov, MD, Lockheed, Convair, Shorts and more. How can I do this. Is there a registry field or something. Or and add-in. If so please link to a way if not can oyu please tell me where I might be able to or a program that will allow this or any other way to do this. Thanks

Andrea,

I hope it's the same column of the second line every time.
This would be a VBA macro that you would run one time. If you are familiar with coding macros, it would be very easy for you to do. If not, and you are interested to learn, it would take some time, but it's a good project for you to start with.

the issues you need to address (even if someone else writes the macro) are as follows:

1) How can the program identify a cell that needs to be moved
2) Is the "last column of the first line" always the same column

You know, a good way to do this would be to manually go through and have a macro assigned to a key that you press when you have selected a cell that needs to be moved up. That takes care of problem one. The other may be easy if there are not blank columns in any row. I may be able to help you more, please post again with more info.

Andrew,

There is a macro someone else wrote that will accomplish what you need, but you will have to set it up. I've seen it several times in Google searches. Here's a link to one:
http://www.mrexcel.com/archive/Formatting/12054.html

Usarian M. Skiff

I'm not officially any kind of consultant or anything, but if anyone needs something quick done in a day or so, let me know what you need, and I'll tell you if I can do it. Email me at usarian@comcast.net

What formula should I use in Conditional Formatting if I was a statement like this...

if this cell starts off with the number 54 and the value of cell1 or the value of cell2 is less than 1 then highlight this cell yellow.

If it's better in VB, I can work with that, too!

Thanks in advance!

Just a big thanks. I googled an excel question and found this site. After reading through all of the items, I now have solutions to several problems that I had given up on. Right ON!!! Thanks!!

Nya,

well, lets see here.. I'll take that literally there and put it like this (I'll call your "cell 1" and "cell 2" A1 and A2.

first switch the mode in the conditional formatting dialog to "formula is"

=OR(A1<1,A2<1)

then click the FORMAT button on the conditional formatting dialog, select the PATTERNS tab and click a yellow color.

OK everything out, and you should be set!

Usarian M. Skiff

Hello!
Is there an option using only an even cells in the column (Excel)?
Unlike matlab this program does alot of problem in reference to non consequent cells.
Great Thanks.
Yevgeny

I have a database with a column of names & a column of dates. can someone tell me how I set the column of dates to change cell colors or text colors from 90 days to 61 days out, to turn yellow, from 60 days to 31 days out, to turn orange, and from 30 days out to current date to turn red? Thanks

Steve:

set the conditional format mode in the cell containing the date to cell value is, then put the following formula in the white space:

set the format to pattern, red

click add format

set the format pattern to orange

click add format

set the format patterm to yellow

hope that helps!
Let me know how it works out!

Usarian M. Skiff

Is there a method of conditional formatting that will only use part of the data in a cell and disregard the rest? I have a spread sheet that IDs the due date of an assignment in red, yellow, green (overdue, due in one week, due in greater than a week.) The issue is to keep the original due date even if a new one is assigned. (two or more dates in one cell, with the old date struckthrough)

I am trying a simple conditional format and it doesn't seem to want to work right. I have a table with start and stop dates in C & D and dates along the top in 7 day formats. My first two dates in C & D are 10/31 to 12/5, second dates are 11/15 to 1/8. I entered this formula: =IF(LEN(F$5)>0,AND(E$5>=$C6,F$5<=$D6),E$5<=$D6) and set it to turn blue if the date above fits the dates in C & D.

Well, in the first row (10/31-12/5) 10/31 to 11/28 turns blue (I don't know why 12/5 won't turn blue). Then 12/26 and 1/23 turns blue (why?). In the second row (11/15-1/8) only 12/26 turns blue. I'm confused.

I got the above to work by ultering the formula like this:
=IF(LEN(F$5)>0,AND(E$5>=$C6,F$5<=$D6,E$5<=$D6)) But it refuses to work if you have start and stop dates that go into the next year like: 11/15 to 1/20.

Does anyone know why that is?

Ignore my last post, some of my text doesn't show and the formula is messed up. I altered the formula like this:
=IF(LEN(F$5)>0,AND(E$5>=$C6,F$5<=$D6,E$5<=$D6)) and it worked, but if I had start and stop dates that went into the next year, (11/15 to 1/19) it doesn't work.

Does anyone know why?

Carol,

There are several methods to use part of a cells contents. Which one you need depends on what kind of data is in the cell and how it is arranged. I'm guessing that the original due date is the furthest to the left. If that is correct, you can use the left() function (or the right() function if it is on the right).

I use a funky thing I figured out a long time ago sometimes. It goes something like this:

LEFT(A1,len(a1)-4)

this excludes the right four characters of the text in the cell.
RB0028 would be RB
SCA0136 would be SCA, etc

hope this helps!

Julie:

I'm glad you got it to work! As for why, I really don't know.

Basically, a conditional formatting formula looks for a boolean (true or false) value, if it gets a true, it activates the format.

logically, your statement seems to look for a non-blank value in F5. If it gets one, it evaluates to see if E5 is greater than or equal to C6, and if F5 is true (non-blank again), which it will be. actually, you could simplify your formula to:

=AND(F$5,E$5>$C6)

and get the same result.

Regards!

Usarian M. Skiff

Usarian,
Thank you for the quick response, but apparently I am still too much of a grasshopper to grasp the finer points of Excel/algebra. Should the formula look like this:
=RIGHT(c11,len(c11)-8)(C11-TODAY())

Usarian,
some of my comments were lost in the last posting. It continued...If I want cell C11 to change color based on the left date? The cell actually has 08/05/04 04/28/04 in it w/ the right date struck through. Or should the formula be closer to this? =(C11(RIGHT(C11,LEN(C11)-8))-TODAY())<14 Regardless I'm striking out. Thanks again.

Hello everyone,

Usurian seems to know his stuff pretty well. I hope I can find an answer. Here is my question, prefaced with examples of my existing excel formating/formulas:

I've created a employee scheduling sheet, for two campuses that I manage. On the left of the sheet is where the employees' location code, name and start/end times are entered. The formula below then calculates the shift hours and creates a Gantt-like bar across columns indicating 1/2 hour increments, like so:

=IF(AND(TIME>=START,TIME<=END),CONCATENATE(LEFT(LOC),"-",NAME),"""")

The output is a combination of the the LOC code and the employee's name, like so: "1-John". However this result shows up in EACH cell that fits these parameters. It creates a bar effect, but is rather "busy" looking.

Each cell in this section of the sheet is also conditionally formatted to color the each cell as orange or purple, depending on the results of these conditional formulas, which are based on the LOC-ation code, thus:

=SEARCH("1",I59) Purple, or:
=SEARCH("2",I59) Orange.

The LOC code 1 or 2 determines this. This works fine, but I'm a perfectionist, and what I'd like to do is have this occur in a way that the created "bar" effect is merged into ONE (1) merged cell that spans each indicated shift.

SO FINALLY MY QUESTION. I've checked and it appears that no formula will automatically merge and center the employees' names. Is VBA therefore the only way to do this? Being a noob at VBA, what would that code look like? Does anyone know of someready made code somewhere that might yield that solid "timeline" bar effect that I'm looking for?

Any help would be appreciated.

Oscar

In reviewing my post, I see that my example of one of the formulas was cut off. Here it is again:

==IF(AND(TIME>=START,TIME

Third time's the charm, I hope:

=IF(AND(TIME>=START,TIME<=END)
,CONCATENATE(LEFT(LOC),"-",NAME),"""")

Again, this is the formulat that populates the target cells.

Oscar

EZ RECIPE for MORE CONDITIONAL FORMATS
Right click on the sheet Name tab and select "View Code" then paste the dollowing code between
Private Sub Worksheet_Change(ByVal Target As Range)
AND
End Sub

Save, Press Alt+Q to return to Excel.

Code:
'From Ctibor Sturc, ctiborsk@yahoo.com
'Make sure the range is not D:D, otherwise you may spent
'a day witing while it goes through all lines :). If your range is too
'big and it takes too long to process, consider to make this as a macro you
'can run by hand or so. Or enhance this little dirty script :)).
'
'cel.Font.ColorIndex = 1 - delete this if you do not want to change font
'cel.Interior.ColorIndex = 0 - delete this if you do not want to change cell background
'cel.EntireRow.Columns("A:D").Interior.ColorIndex = 0 - delete this if you do not want
'to fill beckground of the entire row within columns A and D. Or modify it.
'
'Copy entire ElseIf condition if you need to add more strings or numbers.
'Use Color = (###.###.###) or ColorIndex = # (1-56) as you need.
'
'Enjoy, your boss will love you.

Dim cel As Range
Dim x
Dim isect
'following two lines simply optimize speed and applies formating even if you use
'mouse (+/- one column above input column), arrows or tab to move outside the
'input column (in this case D1:D100)

Set isect = Intersect(ActiveCell, Range("C1:E100"))
If isect Is Nothing Then Exit Sub


For Each cel In Range("D1:D100").Cells
x = cel.value

If x = "" Then

cel.Font.ColorIndex = 1
cel.Interior.ColorIndex = 0
cel.EntireRow.Columns("A:D").Interior.ColorIndex = 0


ElseIf x = "a" Then

cel.Font.ColorIndex = 3
cel.Interior.Color = RGB(253, 133, 130)
cel.EntireRow.Columns("A:D").Interior.Color = RGB(253, 133, 130)


ElseIf x = "b" Then

cel.Font.ColorIndex = 8
cel.Interior.Color = RGB(252, 194, 131)
cel.EntireRow.Columns("A:D").Interior.Color = RGB(252, 194, 131)

ElseIf x = "c" Then

cel.Font.ColorIndex = 9
cel.Interior.Color = RGB(232, 231, 172)
cel.EntireRow.Columns("A:D").Interior.Color = RGB(232, 231, 172)

ElseIf x = "d" Then

cel.Font.ColorIndex = 10
cel.Interior.Color = RGB(129, 254, 214)
cel.EntireRow.Columns("A:D").Interior.Color = RGB(129, 254, 214)
Else

End If

Next

whew! There's a bunch of posts here today! My home computer is down (I think my 2-year-old tried to feed it again..)

Lets take a look:

Carol-

Well, Excel aparently will not classify a date as a date when there is other stuff in the cell. It just sees a string of text. Normally I would use greater than and less then and equal to, but when it sees 08/05/04 using LEFT(C11,8) it can't compare it to TODAY() because it wont see C11 as containing a date, but a text string.
sorry.

Oscar-

I would actually recommend you check out using a pivot chart. If this is something you havent messed around with much, you're in for a treat! They're phenomenally powerful, and pretty too :>
A trick I had to learn with them is that when you use the wizrd to create one, and it asks for the source data, select all the columns on the page that contain information, relevant or not.

Anyway, good luck! (lemme know how it goes!)

Usarian M. Skiff

This is not exactly a formating question, but it is a conditional one. I would like to open a comment element in a cell to be filled in by the user if a cell is above a certain limit. Is a macro required...if so, any tips on getting started would be most appreciated. Thanks.

Hello,
I'm trying to build a budgeting spreadsheet that uses several pages that build off the first. In other words when you enter a value in the first page it is carried into the next pages. I have two types of pages, actual numbers and variances. You enter the actual number and then the variance is calculated on the following pages. The problem I have is that when there is no number in a cell on the actual numbers page, it shows the negative amount of the budgeted number on the variance page. In case this is confusing, i find the variance with the formula =actual-budget, so when there is no actual value i get -budget. I want the variance pages to show nothing or 0 in the cells until an actual number is entered. Is this possible?

unable to delete conditional formatting due to cut/paste on several cells within the range. I would assume this is the reason the conditional formatting is greyed out in format menu. Can you please advise a workaround for this. Thanks

Shaun,

Try "=IF(Actual>0,Actual-Budget,0)" -- replacing Actual and Budget with the appropriate cell designations. Of course, if you occasionally have negative actuals (i.e., an unexpected cash infusion, whatever), use not equal instead of greater than.

Topic: Conditional Formatting

I have a grading sheet. Each row is a task to perform. Each column is a day of training. Each task is graded with an S or a U. At the top of each column is an overall grade of S or U. I can give someone a grade of S even though they have a U for one of their tasks. I would like to shade the overall grade cell in red if there were any U's given for one of the day's tasks. The tasks are divided into groups of similar tasks so I imagine I'll have to search more than one range of cells for any U's that might have been assigned. I've read up on VLOOKUP and IF functions to put in my conditional statement, but no examples seem to fit. I'd appreciate any help. Thanks.

Gordon,

First, let me tell you how I understand your question. Each column is a day, each row a task. Similar tasks are grouped together, BUT all grades are still in one column (i.e., every task for Monday is in Column B, Tuesday is in C, etc). If you have multiple or split columns for individual days, then just let me know and we'll tweak this.

Choose the first cell you'd like to conditionally format; Go to: Format>>Conditional Format. Condition 1 >> Formula Is. The formula to input is "=CountIf(B2:B10,"U")" (w/o the external quotes, where B is the day and 2-10 are the rows with the task grades). Click the format button and choose the formatting you prefer -- you will probably want to go to Patterns and choose the appropriate fill color. You can Autofill all the column heads with a simple drag after you set up the inital formatting.

P.S. -- Let's say that you want to provide an overall (all days) grade of S or U, and you want to make sure that there are no more than, say 10 U's total for the entire class. Pick the cell where you'd like to put the final grade, and make the conditional formatting formula "=IF((COUNTIF(B2:F10,"U"))>10,1,0)" (where B-F is the range of columns, and 2-10 is the range of rows), and again pick the appropriate formatting.

Brad,

Thank you. Your recommendation works like a charm for the first range of cells. I need some further help with the same forumula. Using your range as an example I need to find "U's" in ranges B2:B10, B19:B26, and B32:B39. Can the CountIf formula check those 3 seperate ranges looking for "U's"?

Thanks again.

We can do that;
=CountIf(B2:B10,"U")+CountIf(B19:B26,"U")+CountIf(B32:B39,"U")
Hope that helps.

In the ... "Tools - Options - View - Window Options" ... I deselect all options except the "Vertical Scroll Bar". There have been many times I find all the options activated in every worksheet ... which displays zero values, sheet tabs, etc. I do not know who or how they were activated globally to begin with ... and since the only item I want selected is the vertical scroll bar ... I end up going through the entire workbook deselecting all others.

Q: Is there a way to globally deselect all the Window Options throughout the workbook?

Hi Usurian, just stumbled on your site through a Google search, Great resource.

I'm trying to format a table, where each cell contains a mix of numbers and letters, according to a single character or letter.

ex.: cell A1 contains c2x, A2 contains c2y, A3 contains ax3, etc.

I would like to format all the cells that contain "x"

Ultimately I would like to assign this to a macro button.

Seems simple enough, but I just can't wrap my brain around this one.

By the way, I'm using excel 97.

Daniel

Hi I want to check a range for any of the following values "A" or "B" or "C". If I see any of the 3 values I want to set the top cell with "X". How can I do this? I am able to check for either one of the value but not for all of them by the following

=If(countif(A2:A6,"A")>0,"x","")

I want to know how I can use COuntIf and OR together in order to obtain this.

Neeraja.

Neeraja:

=IF(COUNTIF(A2:A6,"A")+COUNTIF(A2:A6,"B")+COUNTIF(A2:A6,"C")>0,"x","")

Trying to track if we are late (less than 55 minutes to departure is late).
Let's say A1=09:00 (time truck is supposed to leave)
A2=08:55 (time we finalize paperwork)
If A1-A2 is less than 0:55 minutes, then we are late. How do I get the A2 cell to turn red? I understand conditional formatting...but I can't get this to work!
thanks,
J.

Jason,

=IF(55>(A1-A2)*1440,1,0)

This means if the difference of A1-A2 in minutes (the *1440 converts it into minutes) is less than 55 minutes, format, otherwise don't (actually, it says if 55 minutes is greater than the difference ..., but it works the same way).

I have three columns. I want to format conditionally as mentioned below:

A B C
12 23 29
22 21 45
88 99 76

in the above table, I want numbers of column B to color green if greater than A and column C to color green if greater than B

which means 23, 29, 45 and 99 should be Green.

can you please tell me how to do that

dipes:

Highlight column B, and do:
Cell Value Is: Greater Than: =A1
(If you do not want to do the entire column, select the appropriate cells, and where I have =A1, put =A#, where # = the row number of the current selected cell).

This will format every cell in B to compare to the appropriate cell in A, and format if the value is greater. To do column C, either repeat this setup, or just select column B, copy, select column c, paste special, Formats.

well, in this way the values in column C compares with that of column A. I want values in column 'B' to compare with column 'A' and values in column 'C' to compare with column 'B' and so on...

for one more example which may help:

A B C
22 55 45

in this case - 55 should be green, but not 45 (in second row). but, when i do as you suggested, both 55 and 45 gets colored, since both are greater than 22 !!

please help.

I'm back! Sorry, computer broke, looks like Brad picked up pretty good!

dipes,

This is one of those odd things about conditional formatting:
it defaults to absolute cell references (that is, it always automatically puts the $ in the reference, like A1 would be
A$1$) when you use the mouse to select a cell. Try what Brad said again, but this time, TYPE A1.

Then do the same to C:C as you probably did last time.

Daniel:

There IS a way to do this using a formula rather than a macro, but I haven't had a need for it.
A simple VBA script would do the trick as you suggested, the crux of that being the following lines:

for x = 1 to len( {your cell} )
if mid({your cell},x,1) = "x" then
With Range("A1").Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
end if
next x

This does not include anything about cell selection, etc.

Good Luck!

Usarian M. Skiff

It worked !!!
Thanks

- Dipes.

I am trying to colour cell when the data changes in them. For example I want all cells with 21 red and then if the cells change to 25 half way down the sheet I want them to change again. I don't know the numbers that are going to appear as they are pulled from table so I need a general formula. Hope someone can help! Thanks

Depesh:

Basically you want to use Conditional Formatting, so you came to the right place!

Just read the instructions on the top of this page and you'll be on your way.

Usarian

Thanks, David (July 2,2003 06:58), you da man!

In my spreadsheet, Column A is a list of index numbers (1-25), with a matching list of names in Column B. Column C is a shorter list of randomly generated numbers (using =RANDBETWEEN(1,25) ). I needed the peoples' numbers (or names) to be highlighted any time their number appeared in the list of randomly generated numbers. (I'm doing a "simple random sample" for my statistics class.)

As David said, "Conditional formatting based upon testing whether a cell value appears elsewhere in a list is relatively simple. Use the COUNTIF function, so conditional format where
formula is = countif(range name of list, cell reference of current cell)=0 and set the colour you want it to be if the cell value doesn't appear in the list."

I originally tried it this way:
Formula is: =COUNTIF($C$2:$C$26,$A$2)>=1

but it didn't work quite right. The trouble was the absolute reference of cell A2 ($A$2), so I changed conditional formatting for the cell like this:
Formula is: =COUNTIF($C$2:$C$26,A2)>=1

After that, I was able to copy the formatting to all the other cells in Column A, and now any time the random numbers change in Column C, the matching person's index number is highlighted in Column A.

Thanks again, David!

How do I do a conditional formatting on cell value

I want
if cell value is >.25 turn green
if cell value is

Open "Format" menu.
Click on "Conditional Formating"
Condition 1:
Keep first block as "Cell Value Is"
Change second box to "greater than"
In the third box type ".25"
Click on the "Format" button in Condition 1.
Pick the "Patterns" tab in the "Format Cells" dialog box.
Click on the color square you want and click on OK
You are now finished. If the number in the cell is greater than .25 then the cell should fill with the color of your choice

Say I have a column filled with postal codes. I would like to know how many of the postal codes start with L and M. How would I go about doing this?

Example
V6Z 2Y4
L3T 7P6
M2N 6Y6
L5Z 3Z3

I should have an answer of 3 returned to the target cell, but I'm now sure how to check the first character of the postal code.

Help is appreciated.

Thanks

I have created my conditional formats, so that it changes the colour if a row depending on the value in a cell. Now I need to copy the formula/conditional formula to the rest of the sheet, but when I do this end op overwring existing data!

Can you help please?

P.S. My format is dependent on whether a date has been entered into the cell i.e. =($a15>1)

TJ-

Well, I would suggest taking the easy way out of this one and just making a column that lists only the first letter of each cell in your column. You can hide it, or put it on another page, but it would be easier than a making a macro.

in the new column you would drop copy the following (substituting the "A" for your sheet/column)

=left(A1,1)

Bazdaa:

Usually after you copy, before you click anywhere, look in the lowest right hand corner of the selection and there will be a little box you can click on. It will open up into a Paste Special menu. You want to select "Formats Only" on that menu.
You can also paste from the EDIT--> PASTE SPECIAL menu bar item and select formats only from there.

Usarian

Hi

I have a set of data, each record(row) of which has a date in the format dd/mm/yyyy (i.e column D). Is it at all possible to apply conditional formatting to entire rows where the date value is greater than or less than a fixed value e.g. 01/10/04. (I don't want to have to use the TODAY() function if I can help it).
If so, can you tell me what the formula would look like?

Many Thanks

Paul

Paul,

Well, the format of the date can be arranged any way you wish. (right click the cell or row header, etc. and click Formats and select the Numbers Tab).

But, getting on to your question,
to apply a static conditional format to a row or column containing only dates, select the row/column you want,
click conditional formatting on the menu,
you want the CELL VALUE IS mode, then LESS THAN (or Greater Than if you wish), then type your date. That easy! :`)
Let me know if you have any problems.

Usarian

I have a question and i think im spending too much time on this.
I have data in column A and in column B. I want to do a conditional format to do this: tell me if any of the data in column B appears in column A. If so then Highlight the ones in column A. what formula would I for coditional formatting????

with A2 selected, in the conditional formatting dialog, with formula mode selected:

=countif(B:B,A2)

drop copy that thru the whole column A and you should be set!

I used your excellent tutorial to help me with displaying incentive points at the school I work at. Pupils scoring above or below average (mean) are shown with a different coloured cell.
Thanks for your help

I have a spreed sheet I use to direct my staff as to jobs that need done. I need to hide the ones that are complete. Is there a way to formatt, that when you enter a completion date the the row would the hide?
Thanks Denny

How would I make the formatting conditional on whether or not the cell contains a formula or value?

here is if it has a formula, what about not.

=GET.CELL(48,INDIRECT("rc",FALSE))

Here's another.

I want to change the colour of a cell based on whether the cell is locked or unlocked. How do I go about getting excel to do it for me?

Thanks in advance

Cirano

I am trying to shade cell A5 light grey. This shade showes that the task has been completed. The cost for this task is in cell A3. Is there a formula that says: When cell A5 gets shaded insert cost figures from A3.

thanks.
Sadie

I am not an advanced Excel user, and I need to do something rather difficult. If an "X" is in one cell then I need the other cell to maintain its text but simply have a fill color. Is this possible? And, if so, can someone explain it to me in black and white? Thank you!!!!

I currently have a training data base set up in Excel. I have entered dates of the innicial training. I want the cell to turn red 1year after date. I also have date already listed I.E 11/3/03 and I want this to change to red after the year is up. Can I have the cell turn red automatically after a year is up? or do I have to do this for each date?

Hello,

Brandy Castro,
There are really good directions about doing this at the top of the page.
If it just isn't making sense, write again and I'll try to help you out.

Joe,

You can use greater than and less than the same as normal numbers. For example, if your date is in cell A1 you would select the cell, open the conditional formats dialog, change "Cell Value Is" to "Formula Is" and type the following formula in the box:

=YEAR(TODAY()-A1)-1900>=1

Cirano, Sadie
You both need to use VBA (a macro) to do this.
If you are interested in taking this route, click my name to email me directly.

Usarian M. Skiff

Is it possible to do this with time? I've been trying to get it to work for deadlines, and I've hit a wall when the deadline is 12-midnight; the cell will always be whatever color I specify, regardless of the time. I don't want to have to put in the date because it wouldn't be practical to enter the date into 500 cells, then change the deadlines for each institution... THEN turn around and enter the completion times with the date in front of them... ARGH! Headache... any ideas?

Could you use 11:59pm in the code?

Is there a way to assign a colored font format to a cell conditional on the cell containing a formula (but not a value or text).

I'm trying to make a fuel guage type spreadsheet.

I have an input cell that takes in the amount of material left, and then I have 100 cells that change colour according to this value. I want them to change when the number is >=100 , >=200 , >=300 .... >=9800 , >=9900 , >=10000 .

It will take me a long time to put in the formatting for each individual cell, so is there a formula I could use and fill across to each cell that would do this for me?

To All who wish to have a format conditional on whether a cell contains a formula:
[assuming cell A1]

FORMULA mode,

=LEFT(A1,1)="="

Stephen:

You need a macro. You may email me directly for help with this topic, I would be happy to be of assistance, just click my name above.

Usarian M. Skiff

I am trying to format a cell so that it can only accept numbers up to 20 digits in lenght. I am going to format cells and selcting Custom and adding 20 "#" in the field. However when I test for some reason every digit after the 15th Digit is rounded up. Can anyone help??

I'm not sure if I'm in the right place here (probably not!), but can someone point help me figure out how to concatenate multiple cells based on a condition? Basically, is there a way to do a "concatenateif" much like there is a "sumif" and format the concatenation so that there are commas and/or spaces between the text values? I've looked everywhere online for answers to this but I can't find anything useful.

thanks!

I have a worksheet with 3 different conditional formatting

1. Rule
Formula Is =WEEKDAY(F4;2)>6
2. Rule
Formula Is =F$1=""
3. Rule
Cell Value Is between =$B4:$C4 and =$D4:$E4

The last one is not working because I cannot use a direct reference. How can I do it with formula ?

Thanks

Dips -

I'm thinking it's not possible to do what you are doing without some pretty extensive programming. There may be a simpler way to do this though: by using a data entry form.
Forms are very easy to create and program in VBA. If this method is of interest to you (honestly, depending on how many fields need entry at a time, it may not be practical in the end anyway), visit my website by clicking my name above.

David -

There, as you guessed, is no direct way to CONCATENATEIF. You CAN use an if statement to accomplish the same thing though in this case. (that is, if I'm understanding your problem correctly..)

For example, if you are looking for, say address entries that may or may not be in the U.S. You may want to include the Country name in column J if it's a foreign addy, but only street, city and state if domestic:

=IF(J2<>"USA",CONCATENATE(B2,C2,D2,J2),CONCATENATE(B2,C2,D2))

Markku -

Are you using Open Office or Star Office? They use the semicolon (;) instead of a comma in the commands, like your use in Rule 1.

Rule 3 isn't syntaxed in any logical way. The value of the cell cannot be compared directly to a range. You must extract the values you need from the ranges specified. Use MIN(), MAX(), AVERAGE(), SUM(), and the like.

Hope this helps! Write back all!

Usarian M. Skiff
For help automating Excel, click my name above and email me from my site.

Hi

Thanks for the answer!

No, i don't use Open Office or Star Office. It's maybe my language settings? I am from Finland.

I had to make compromise and I changed the Conditional Formatting rules like this and now it works!

1. Rule
Formula is =OR(F$1="";WEEKDAY(F3;2)>6)
2. Rule
Cell Value is between =$B3 and =$C3
3. Rule
Cell Value is between =$D3 and =$E3

My first example would work with 4 rules :-D

Markku

Markku -

Ok, I get it. I think you could still use all four conditions in this case, since the desired formats are the same for the two conditions. Use the OR() command to accomplish this. eg:

(in formula mode, assuming A2 is your cell, when the cell is copied the reference will change)

=OR(AND(A2>$B3, A2<$C3), AND(A2>D3, A2

Hope that helps a little,

Usarian M. Skiff

I have a database of Files in Excel, some with duplicate entries of one file name. I set up formula that so when there are multiple listings of a file names in a row (down a column), they are given the same key #. To see multiple listings easily, I set up a conditional format that if a key# is the same as the cell above or below it, it turns grey.

When I sort the database by different fields (other than file name), I loose the conditional formatting because the keys #s are no longer in numerical order. Effectively, when I sort the database, I can't tell which files are duplicates or not.

How do I paste the colored "format" of my conditional formatting, without pasting the condition itself?

Thanks!

Sally,

The solution to your problem is a macro.
Click my name at the top of this message to visit my website. From there you can email me your Excel automation question. I will be happy to help you (at no charge of course).

Usarian
Excel Automation

Is there a way to apply conditional formatting to a cell if the source of the conditions are on a different sheeet?

Thanks,
Matthew

Is there a way to cause an Excel cell to flash when the time in the cell is greater than a time value in another cell?

i need to have excel change cells based on letter combonations. example if the cell has nmcm or nmcs it needs to be red. if it has fmc it needs to be black. if it has pmcs or pmcm it is blue and if it has bt or bq it is green...

how can i set this up?

Jamie,

Follow the instructions at the top of this page, but since you have multiple considitions and only a few formats, you will want to use the FORULA IS setting and use the OR() command. eg.

=OR(A2="nmcm", A2="nmcs")

You will have an issue with the fact tha conditional formatting only has the capability of doing 3 different formats though.
There is VBA code floating around the web to increase this usin macros.

Doc,

There is no way (using conditional formats) to make cells flash.
There is some vba code floating around the web for this as well.

Matthew,

Unfortunately, no. What I do to bypass this limitation is set up a hidden row that is just repeating the information from the other page for my conditional format to use.

Usarian M. Skiff
Excel Automation
http://www.freewebs.com/usarian/

Thanks for your help group.

Good Morning,

I have a question about conditional formatting. Is it possible to have a cell highlight if it matches the value of anther cell in a seperate list?

Thanks,

Matthew

Is there a way in Excel to prevent conditional formatting being "overwritten" when you paste into cells formatted in this way. I know using Paste Special/Values will work but can a simple Paste be used while still maintaining the original conditional formatting being deleted.

Thanks for any help!

William

Here's one: can I use conditional formatting based on the logical result of a test in cell A1 to clear the contents of cell B2?

That's it! Thanks for any ideas.

Dernon

i have a row with 28(dollar value)cells (A1:A28) feeding an average column (A29). need to take the average column plus and minus 5 percent (A29+5% and A29-5%), have the program select the appropriate cells, then format the cell/cells with yellow background with black letters.
Regards,
Lane

How Can I put two Formulas that can work for the whole column
using if Connditional
Example
Let us take this a list as sample the grade of the studets A B c Average
1 0 3
1 3 -
The weighting
Factor is 1 2 2
Now what I want is that ,I want to Calculate the average of the grade of the Students.
0 indicats the grade of the student whose score is least.
- indicates the the student who do no take the the exam becouse of some reasons .
The Formula of the average is
(1*1+2*0+2*3)/(1+2+2) this is for first colommon but for the second colommon we want
to jump that cell which containes - .
So in the formula to give us
(1*1+0*2)/(1+2)
But remmember that - may Also appear in other
colommon.So how can I put this in Conditional
in Exel.

Is there any to set a conditional format so that an image or autoshape is displayed instead of a colour when a certain value is entered. Or copy the values of one cell to another

Hey !

I am using conditional formating for dynamically change excel column color when i apply same condition to the another column then i change color first column dyanimacally then it affect another column too

How i can fixed this problem

wajahat,

It sounds like you have the same condition for both columns. If you copied the conditional format from one column to the other, the cell references default to static values (with the dollar signs).

Is there a way to get a cell to change colors or fonts simply by clicking on the cell regardless of the value in it and changing back when not clicked on?

Thanks

Yes. It's a really quick macro. It is not possible to respond to click events in conditional formatting though.

Click my name above to email me and I'll be happy to oblige!

Usarian M. Skiff

I'm trying to do a conditional format on a cell which has the following conditions
cond 1 - if the cell (which is a formula result) = 5 than turn green
cond 2 - if the cell (which is a formula result) = 10 than turn yellow
(these two conditions work fine it is the third one that we can't get to work.
cond 3 - if the cell (which is a formula result)= 15,20 or 25 turn red we have tried the following statement but it doesn't work properly
=or(15,20,25)

I'm not sure if this is possible or not but I appreciate any help that can be provided.

Dave Palmer-

Yeah, this is one of those stupid annoying things.
you have to make each element of the OR() statement a complete boolean expression. For example, if your format was in cell A1:

=OR(A1=15,A1=20,A1=25)

If you put the formula at the top of your column and drop copy it, it will have the correct cell values only if you type the cell references. If you click the cells to get them in there it will put the $'s.

Anyway, I hope that helps!

Usarian M. Skiff

For help with vba and macro related questions, email me at usarian@comcast.net

I have a spreadsheet i would like some help with. Would it be possible to email you the spreadsheet for you to look at.

Thanks
Jid

I prepared a big boolean formula in a cell.
I wanted to cut/paste this formula into the condition field in order to modify the background-color.
I can't paste into the condition field !?
What's wrong ?
Is there a kind of "=FormulaOf(a1)" function ?
Is it possible to "load" conditional formatting from any vba-code ?

Oops ... posted too quick
"I wanted to cut/paste this formula into the condition field in order to modify the background-color"
... of many other cells depending on t