I'm working in Excel 2003.
I'm looking for a way to use conditional formatting to flag a cell in a list if the user enters a value that already exists in that list. Just trying to keep any values from being entered twice.
I understand how to set cond form to flag a cell, but not what criteria to enter to activate the formatting when a value has already been used.
Some sort of =IF(COUNT...?) function?
I made a planner for shared use.
Is there any way users can cut and paste while keeping the Conditional Formatting of the source cells intact? It is the same as copy-paste and then delete the source cell contents after copying.
Excel 2003
I am using Excel 2003. I have a spread sheet and I am trying to use conditional formatting. But I can't figure out how to use conditional formatting across multiple columns and so I have been doing each column individually which has been kind of a hassle. I also am trying to figure out how to put in a formula so that every occurrence of a certain text will show up a certain color. I know that I can just do "cell value is", "equal to" and click on the cell, but the information in this spread sheet is organized alphabetically and is going to be added to frequently therefore every time I add information it re-organizes. This then messes up my conditional formatting because the text value that was in the cell gets bumped up or down.
View 2 Replies View RelatedI was wondering if it is possible to have cells formatted based on the presence of an autofilter? I have a large worksheet, and to clean it up I would like to make the entire sheet (minus the category headers) invisible. Then, when the user applies a filter, the cells become visible.
View 1 Replies View RelatedI am using excel 2003. I need to conditional formatting for the following:
Based on the column A, if it is USD, the next column show $ and the cell in blue
If it is JPY, the next column show Yuan sign and the cell in green
If it is Euro, the next column show Euro Sign and the cell in yellow.
How to make that happen?
Excel 2003.
I am creating a heat map using two variables from a questionnaire: firstly the number of people impacted by a question; and secondly, the level of the impact. Respondents are asked to give these values in two adjacent cells. These are then multiplied to give a weight eg 1000 people x level 3 impact = 3000. I have used Conditional Formatting to colour the cell according to the impact level (0 = none - white (default), 1 = moderate - tan, 2 = significant - orange, 3 = high - red). What I want to do is then change the font size to give an indication of the number of people impacted, eg 1 - 1000 font 8, 1001 - 3000 font 12, 3001 - 8000 font 16, 8000 - 2000 font 24 etc. The size of font and colour will then represent the number of respondents and their impact.
I can't use Conditional Formatting as the value of 3000 could be derived from 1000 people at level 3 (red), or 3000 people at level 1 (tan). Is there another way of setting the font size depending on the cell value, eg by VBA?
Excel 2003
On one of our computers at work, Excel is acting especially strange. The conditional formatting in Excel on one of our computers is not working. In addition, we cannot fill a cell with any color or change the color of the writing. We can click a color for the letters and type it in, but immediately after another cell is highlighted, the writing goes black again. When we try to highlight multiple cells with the cursor, the cells are black instead of the normal color. This happens to not just spreadsheets that are new, but existing spreadsheets on our shared drive from only this one computer as well. No other computers are affected this way. All other computers show the spreadsheets fine and do not have any functionality issues.
I use Excel 2003. I want to do a conditional formatting on a cell (e.g. A1), which value is from an If() formulas. The value could be a percentage or a text “DataNAvail”. The conditional formatting on A1 is based on another cell’s value (e.g. B1), which is also a percentage.
Since in Excel 2003 there are only three options for conditional formatting. I used two for green (A1 greater than B1) and yellow (formulas in A1 equals AND(A1<B1, A1>=B1*0.75)). I want to set A1 to red when its value <B1*0.75 or is “DataNAvail”. Since they are not the same type, I don’t know how to do it.
In column P of my Excel 2003 worksheet named: Data, I need the following text in the cells to have the interior colors (as shown below):
Any Situation - RGB = 255,204,0 (with black text);
Apples - RGB = 153, 204, 0 (with black text);
Apples / Rasberry = 153, 153, 255 (with black text);
Jam = 128,0,128 (with white text);
Nectarine = 0,0,128 (with white text);
Nectarine / Apples = 255,255,153 (with black text);
Nectarine / Rasberry = 255,204,153 (with black text);
Orange = 255,153,204 (with black text);
Rasberry = 153,204,255 (with black text); and
Sausage = 0,0,0 (with white text).
The range is P3:P20
I tried using conditional formatting but it only allowed me to use three conditions.
I am having a fit trying to get this darn spreadsheet to do what I need it to do. I have to flag rolling dates on a spreadsheet (i.e. if a training date is to occur every three years, I need for it to change colors according to the date given - like if it is past three years, then it needs to turn red. if the date is in the next six months, it needs to turn green). I have already coded the spreadsheet to change dates every day, but when I use the EDATE function in the conditional response and try to copy it to the cells in the rest of the colum, it won't work.
I have a series of tabs with data, 25 columns by 600 rows, all with numbers. A cell should be red and lined out if its' corresponding cell on the very last tab is less than 50. I've tried to name range the area on the last tab (RawBuyers) and use it in a Formula Is CF on the first worksheet, but I get errors.
I'm seeking help with a CF formula that I can apply to each tabs data range.
I've seen threads similar to my issue, so I apologize if this is redundant (but nothing I've found is doing the trick.)
I work in a call centre and I record my daily figures. My target is 82%, and I would like a bar chart in Excel 2003 to colour the chart according to wether I hit target or not. If my daily figure >=82% the bar should be green.
View 9 Replies View RelatedI'd like to make a conditional design for a row. If a certain cell equals "x" I'd like to color the whole row with grey My version of excell is 2003
View 7 Replies View RelatedIs it possible to protect an inserted text box from being moved, stretched, etc. while allowing data input into the cell/s? I'm using a 2003 version of excel.
I've attached the sheet in question. The yellow text boxes are what I want to lock, yet still allow data input.
excel tip example.xls‎
This refers to Excel 2003. I will be dragging contents of cells to another place on the same worksheet. To make myself clear, the text must be moved from the source cells to the destination cells, leaving the format of both source and destination cells - such as borders and fill - unchanged.
NOTE: I know that I can copy the cells and return to the destination cells to delete the content that I want to move. This wastes time when doing many drags and drops.
NOTE: I know that I can paste special, pasting the Values only. Again, this is a lot of extra clicking or keyboard shortcuts. Again this wastes time when dragging and dropping many times.
I am trying to use conditional formatting to find cells that have the word "managment" in the text. The problem I am having is that the cells may contain a paragraph and the word may be embedded in the paragraph. I need to either high the word or the paragraph. I can get it to work if only the word "management" is in the cell but not when I add other words to it. I have tried using *management* but this didn't work.
View 12 Replies View RelatedI have a spread sheet that shows dates when people have been assessed to be fit to carry out exercise I want to be able to put dates into a cell that are older than today e.g. 07.07.2013 and it turns the cell green then 11 months from that date the cell turns yellow and 12 months from date cell turns red. I can get conditional formatting to do this from the present date but not from older dates .
07,. 07. 2013 = Green
07.06. 2014 =Yellow
07.07.2014 = Red
I'm trying to clear conditional formatting (icon set) that I was experimenting with, but no joy at all. The only function within the "Styles" section is Cell Styles -- conditional formatting & fornat as table choices are grayed-out.
I've tried to delete and re-enter the data, clear formats, clear all, copy and paste, format painter...nothing worked.
Using Excel 2010
I need to "Fill" cells A1 to A10 with a fill colour if cell A1 contains anything (Value or characters)
Is it even possible using Conditional Formatting rather than VBA?
What I'm looking to do in Excel 2013 is have it so a specified cell loads an image based on the results of a range of cells.
For example:
If cell B1 = red
and cell B2 = up
and cell B3 = yes
then cell A5 shows image X.
But then have it be comprehensive enough to do
If cell B1 = green
and cell B2 = down
and cell B3 = no
then cell A5 shows image Y.
My hangups are trying to get different images to load in the same cell. Also, all the examples of this I've found are pulling from one cell where I need it to display an image based on the results of multiple cells.
I have a row of data in Excel 2013 that is roughly as follows :
Cell A2 - Date || B2 - Status || C2 - Description || D2 - Estimate Time || E2 - Comments
Cell B2 is a pick list of statuses --> Open || In Progress || Closed
The conditional rule I want to create is one where if B2 = 'Closed', format the text of the entire row (cell A2 to E2) to turn light blue.
I was successful in doing this --> basically highlighted A2 to E2; conditional formatting; new rule; use formula; $B$2 = "Closed"; format text color blue.
When I do this, now when cell B2 = Closed, the line turns blue.
The issue is I want this condition to apply to row 3, 4, 5, 6, 7 etc ... basically a multi-row list.
I highlight the cells (A2 ~ E2) and then grab the bottom right corner of E2 and drag down 5 rows (creating row for A3 / A4 / A5 / A6 / A7).
The issue is when I set B3 to Closed, nothing happens; likewise for B4 or B5 etc ...
When I set B2 to Closed, it changes itself and all the rows below to light blue.
This used to work in earlier versions of Excel, but I can not see to get this working in 2013.
I'm running Windows XP with Excel 2010. I would like to be able to have a group of 19 boxes of which each box has a unique entry, ie 1, 3B, 5C etc. Each box I'd like to have a way in which when selected only the single or dual unique characters show as a color. See attached spreadsheet with sample. When a cell with lets say 1 is selected, a drop box appears and the color and description are shown. Select a color but only show the 1 and the color selected, leaving behind the description. How can I do this for all 19 boxes. This seems like its a classic conditional format. Unfortunately it appears to be several orders of difficulty than a normal conditional format.
View 2 Replies View RelatedI need to generate conditional formatting in Excel 2010.
For the planned date of material submittable should turn yellow before 10 days ago & planned date of material submittable should turn Red when overdue.
I'm trying to do conditional formatting that applies to an entire column (that way there are fewer conditional rules), but it only applies to a certain range of cells within that column. So what I did was setup the rule such as:
Applies To:
=$B:$Z
Formula I want to use:
=AND(MOD(ROW()-3,49)=38,INDIRECT("B"&ROW()-4)>=0)
If I copy the entire formula into a cell on the spreadsheet itself, it works just fine returning TRUE and FALSE depending on what row I'm on.
However, the above formatting rule does NOT format ANY row. But if I remove the AND function and the first condition (the one that limits which rows it's actually going to apply to) then it will work...OR if I do NOT use the INDIRECT function then it seems to work, it's some weird combonation of using the two together that is causing it to not evaluate to true.
Oh, and I thought of trying to get the formatting to work with the INDIRECT...then I put in the AND function but the only parameter was the original INDIRECT function (see below)...it stops formatting!
=AND(INDIRECT("B"&ROW()-4)>=0)
Office 2010 on Windows 7 64bit
Conditional formatting format: Boarder - bottom boarder
The problem when I insert a raw in the middle of the table the condition formatting is not applied. How can I make excel carry over the conditional formatting?
The only reason I formatted the data in a table because it'll automatically control alternating row background color when insert rows.
I'm currently trying to set up a spreadsheet on excel, when certain documents need updating. Basically I have a column with the dates in that each document was last updated, I want the cells to turn red (fill colour) if the document has not been updated within the last 6 months and orange (fill colour) if the document has not been updated in the last 4 months. How do I do this?
I am currently using Excel 2002 and am on a Windows XP computer.
Using Excel 2013.
I have two columns of numbers. For a row, if the right number is greater than the left number, then color it green, if it is less then color it red.
e.g.
A1 = 100 B1 = 101, then B1 gets colored green
A2 = 100 B2 = 99, then B2 gets colored red
etc.
I have clicked on conditional formatting with A2 selected, then chose A1 for values greater than and chose green. I then copied the formula for all rows. I then did the same for values less than and chose red.
It is not coloring the cells correctly. On some rows when B
I have data in several columns going left to right. In column N I subtract column H from column I and then in column O I subtract column I from column J. I then use conditional formatting to indicate whether column N is greater than 45 and column O is less than 120, using different formats for each. If the cell value does not meet the condition then there is no conditional formatting used in that cell.
I want to subtotal each column to indicate the amount over or under 45 in column N and over and under 120 in column O and I would like to know if I can do it using the colors in each cell. Most rows do not meet either condition but a few rows do meet both conditions and I don't think sorting and subtotaling is not going to give me what I want.
I have about 7,000 rows in this worksheet and I am going to be removing lines and then maybe adding them back and I would like to track the balances as the data gets moved around. I can use Window Watch for that.
I am using Excel 2007, don't know how to write VBA and would really like a solution that does not use VBA.
I have a spreadsheet where we track our contractor's Worker's Comp and General Liability insurance certificate expiration dates. I want the expiration date to highlight in red if it is expired and to highlight in orange if it will expire within 30 days or less.
I have attached a testing sheet similar to what I'm working on. The F column has the dates that need to highlight. I have MS Excel 2010.
I am trying to do a conditional formatting based on the result in one cell but it needs to add up multiple cells first to determine which conditional format it needs to use. I am not sure whether I need to do a formula conditional format or whether to use cell is greater than with =sum() in it. I have tried both but neither seem to be working correctly. I have attached an example of the spreadsheet and the conditions I am trying. I am using 2010 version.
Basically, the closing amount for Monday in prod 1 (cell c4), needs to display in red if sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4) & Fri (R4) is greater than that value or display in amber if the sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4).
I need to complete the same for the whole column for Monday (col C) for each appropriate product. Then the same for column B but this will sum different cells but the principal will be the same.