Excel 2010 :: Icon Conditional Formatting Not Working When Formula Present In Active Cell?
Mar 20, 2014
I am trying to apply icon conditional formatting in a cell. The cell contains the following formula: =VLOOKUP(D20,'owssvr(1)'!O:W,9,FALSE The formula results in a "2", "1", "0" or "-1" in the cell. The icon conditional formatting is not working at all (no icon appears). I have the conditional formatting setup as numbers Green 2, Yellow 1,0 etc based on value. If I delete the formula and just type in any of those numbers directly, it works. I have changed my cells to "number" and it still does not work.
how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40 condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39 I want to add: condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40 condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39 and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
Any way to import and use icon sets for conditional formatting other than the ones provided in excel 2007? I would like to have some circles and shapes in colors other than just yellow, green, red, and gray.
I'm looking to create a conditional format which highlights a date RED if it is within 90 days of today's date (the date the file is opened). What would the formula for that look like?
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.
Conditional Formatting 1 cell with 3 different formulas
I am monitoring tank levels using a program called "PI". I need to know if the tank is rising, lowering or staying the same.
I am using conditional formatting to turn red if high, blueif low and yellow if stays the same. Column B, F and J are tank volumns, Column C is in feet and D is in inches.
A B C D E F G H I J K L 1 TIME LEVEL FT IN TIME LEVEL FT IN TIME LEVEL FT IN 2 7:00 3628 18 11 9:00 3456 18 0 11:00 3321 17 3
AB AC AD 1305 5600 3600 LOW HIGH CURRENT
I started using formals =$B$2=$AD$2 COLOR YELLOW =$B$2>$AC$2 COLOR RED =$B$2
I'm new to 2010 and can't work out the new conditional formatting! I need to be able to enter a letter in A1 and have E1, F1 and G1 automatically show a different letter in each cell (different coloured fonts in each cell as well).
However I am trying to apply this to a whole column but the numbers are not (D5,B5) moving down (to D6,B6), When I use the format painter it is taking too long as I can only merge conditional formatting on one cell at a time and I have 30 colums and 390 rows to apply this formatting to.
I am trying to use the status of Conditional Formatting to toggle on/off protection for a cell.
I have a cell with Conditional Formatting applied if the result of a formula is true. If the Conditional Formatting is applied, I want the cell protection turned on so a user can not change the entry in the cell. If Conditional Formatting is not applied (false), cell protection is turned off and the user may edit.
I'm trying to insert a simple conditional formatting rule - icon sets according to rule percentages I've selected (Green > 90%; Yellow < 90% but > 80%; < 80% red). The conditional formatting does not work once i select okay. For example. The data set shows 95% as red, 83% as red, it's just not working for me. I've tried opening a new workbook, new worksheet and nothing seems to work. I've been able to do insert this rule based on "number" setting but unsuccessful with percentages.
I have a large spreadsheet that I would like to indicate the location of the cells (Change cell color or text color) which contain formulas. My thought is that conditional formatting would be the easiest way...
I am looking to show Month Wise Trend Up/Down/Nuetral Arrows for the Achievement % for each month.
I want to show the Increase or Decrease trend of the % in the next month.
I have Baseline figures (count) and i have count of Mar, Apr, May months. (Column A)
I have calculated the Achievement % by 100%-(Actual/Target) (Column D, G, J for Mar, Apr, May Months)
I want to show the Increase or Decrease Trend Up/Down/Nuetral buttons either by the Achievement % or by the Total Incidents Each month compared to the Baseline Figures (Column A).
I tried to use Icons Set in Conditional Formatting but i was not able to do that.
Is it possible to change color for arrows icons in CF- icon sets? I would really like to have a five arrows pointing in different directions (all in different colors).
Can I do it in conditional formatting or should I go for "Wingdings" fonts and then change font color?
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.
I 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!
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.
I have a date in column A. I want to format to YELLOW column F based on whether column F is blank and 20 days past column A. I also want the formatting to change colors to RED if it remains blank past 30 days. Then, when column F is filled in I want the formatting to be removed.
I've run into an issue where conditional formatting is working correctly 98% of the time, but 2% seems beyond logic. Basically if I have more than 120% of Stock Limit on hand I want the cells to turn green. To do this I entered the following Conditional Formatting formula in cell D4:
HTML Code: =((D4-$C4)/$C4)>=$E$1
I copied the formatting to all the other cells in the range, and most are colored correctly. This is what I get:
As you can see, it works correctly for the most part, however some cells, such as cell H5 in the example, that should be colored by they aren't.
My first thought was that this is an issue with the reference, because as I examine other cells it doesn't look like conditional formatting formulas are updated relative to that particular line, however that is the same case for my red formats and those are correct. I have over 50,000 rows so creating a new formula for each row certainly isn't the preferred option, neither is writing a macro to manually color them because of the processing time. I'm using Excel 2010.
I am trying to use condtional formatting to highlight a row in a table if the order number is NOT on another table, on a different sheet. Example: Order Number 1001 is in my table on Sheet4, Once the order has been filled is comes off my "Pending Orders" table on Sheet1, which is based on a data connection, so it updates once the connection is refreshed. I would like the row with order number 1001 in my table on Sheet4 to be highlighted letting me know it can now be removed, since I have to manually manage this table. I am using Excel 2010.
I have two conditions for a cell. One is that it counts the time elapsed in cell M4, (=now()-J4). The other is whether or not we are still waiting for a response (in cell L4, Y or N). What I would like to do is make it so that when we are still waiting for a response the M4 cell is highlighted yellow if more than 3 days and red if more than 6 days, but if cell L4 is changed to "N", then the formatting goes away. II use Excel 2010.
I have a spreadsheet which uses conditional formatting to highlight cells with certain values. It's works perfectly, except for a single cell. The formatting formula is and should be exactly the same as other cells in the sheet. For whatever reason, this cell will not change with the formatting rules. The cell is not protected, it doesn't have any oddball values or anything that I can see.
The background to this question is that I'm trying to replicate conditional formatting using a user VBA function, because I require a thick border around the cells (and the conditional formatting within Excel only has thin borders)
In the final function, there will be 9 combinations of formatting {Red, Amber, Green} interior with {Red, Amber, Green} thick borders.
The formatting is determined by a number in another cell (the "target" cell), which returns a value 0,...,8
I've only got as far as filling in the interior for the first combination, but the function returns an error "Application-defined or object-defined error".
VB: Function VBA000_003_SetRAG(strTargetRange As String) 'strTargetRange is the reference for the target cell that contains the value 0,...,8
On Error Goto handler [code]....
There is not a problem with the target range, if I remove the two lines relating to rngCaller then the function works OKExecuting the rngCaller.Interior.Color = RGB(255, 0, 0) command through the Immediates window works OKI've tried calling a subroutine & passing the range across
Same errorChanging the range in the subroutine to an absolute range (eg. Sheet1.range("A1")) also causes the
same errorExecuting the subroutine on its own (with the absolute range) works OK
So my guess is that it's a bug/limitation with Excel VBA when trying to execute commands from inside a user function
