# Excel 2010 :: Conditional Format Cell Which Contain Formula?

Jan 16, 2013
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...

View 3 Replies
ADVERTISEMENT
Feb 26, 2014

I need conditional format in column Q which highlight in red, any date over 14 days old.

This column also contains texts (non-dates) and these need to be left alone.

I've experimented with a few bits of formula I've found online, but nothing has worked . .

I'm on Excel 2010.

View 3 Replies
View Related
Jan 10, 2014

I just upgraded to Excel2010 and noticed that if I calculate percentages resulting from other formulas, the result displays as a percentage with 15 decimal places and it ignores the format of the cell. My guess is that since formulas are being used in cells A1,B1, and C1, Excel is ignoring the format. When I hard code the numbers, I obtain the desired result.

Is there a way to change the default of 15 decimal places for formula results involving inputs that use formulas in their own creation? (outside of the Round function in each cell? Within Options?)

The data looks like this:

A1 = formula resulting in 1.76%

B1 = formula resulting in 1.90%

C1 = formula resulting in 1.69%

D1 = C1*B1/A1 resulting in 1.8218147382920100%

The desired result is 1.82%.

View 4 Replies
View Related
Feb 5, 2013

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.

resource tracking ex.jpg

View 1 Replies
View Related
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.

View 3 Replies
View Related
Feb 2, 2014

In Excel 2010, I'm using the following VBA code to unmerge cells:

Code:

Sub defusion()

With Selection

.MergeCells = False

.Locked = False

.Borders.Weight = xlThin

End With

End Sub

This don't keep the conditional format - the first cell, yes, but not the others. How to modify my signature?

View 1 Replies
View Related
Apr 25, 2014

I would like to shade my cell yellow if the priority equals A or A-NEW and ACV=o.

In the data below, items 4 and 13 would be shaded yellow.

I am using Excel 2010.

Priority A, B, C

Consumer UPC

Item Description

ACV Weighted Distribution

[Code] .......

View 8 Replies
View Related
Oct 11, 2012

USING EXCEL 2010

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?

View 2 Replies
View Related
Dec 17, 2011

i am trying to work on a database that has some errors that i want to correct the issue is that some of the cells have data that is listed last, first instead it needs to be listed into first last.

I can not seperate this into 2 fields as it would break the program this database belongs to.

this database is linked from excell to access i am using excell to update an access database

i use office 2010

View 9 Replies
View Related
May 5, 2014

In cell 'I6' if I enter the text "Balance" I would like it to display as "Actual" but if any other text is entered I would like it to display the text entered. How to do this using custom cell formatting. (Currently on Excel 2010).

View 2 Replies
View Related
Jan 28, 2013

I am looking for a vba to use a button to save a file using the cell as its name and save the file in xlsm format. I am using Excel 2010.

View 12 Replies
View Related
Feb 16, 2014

Excel 2010

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

View 3 Replies
View Related
May 9, 2013

I'm using Excel 2010 and I would like to format a cell (say, the font of that cell turns RED) if the value of another cell meets a certain criteria.

View 9 Replies
View Related
Mar 2, 2014

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).

View 3 Replies
View Related
Aug 1, 2014

I am using the following formula on Excel 2010 in conditional formatting

=$D5=VLOOKUP($B5,'Approved Master OLD'!$B:$BC,3,0)=FALSE

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.

View 1 Replies
View Related
Jul 19, 2014

I'm using Excel 2010. Is there any solution which cover requirements given below.

1) I've converted a range in to table format (using Format as Table)

2) Need to Prevent pasting on data validation cell.

3) Need to Prevent change in cell format after pasting data (other than data validation cells as mentioned above) from same workbook or other workbook

4) Need to insert row in the table as per requirement.

View 3 Replies
View Related
Jan 25, 2012

I have a large spreadsheet with a number of columns that are set up for conditional formatting. For example a column of "BUY"'s and "SELL"'s where the "BUY"s are formatted with the default "light green fill, dark green text". I have other columns that are conditionally formatted using a formula. When I base the result on a formula I don't seem to have the option to use the default formats. Is it possible to use the defaults or maybe even how to customize my format to look like the default? It would look a lot better if I had a uniform red/green style throughout the sheet.

View 1 Replies
View Related
Jan 27, 2012

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 am using Excel 2010 and Vista.

View 1 Replies
View Related
Jul 24, 2014

I want to create a system based on the value of a cell appearing as a grade in the adjacent cell. The cell could contain any value from -10 to +10.

the criteria would be

value greater than +2 = 3

value between 0 and +2 = 2

value between -2 and zero =1

value below -2 =0

I can get a formula to work for a single instance but not multiple conditions.

View 3 Replies
View Related
Sep 26, 2009

i have a set of conditional formatting set up for my workbook, the first is simply that if the result is false the cell is blank(white text,white background). the second if positive result,keeps the text black till 1 year has passed. the problem is the third, i.m trying to allow the user to blank out the cell within the year (white text,white background)if certain criteria are met. the idea is that they change the entry in cell D from T to TX or from M to MX and the formula pick this up, but i keep getting a error saying "your formula contains an error", the formula im using is, =if(or($D5="tx","mx")) then i set the format to (white text,white background). its probably really simple but i,ve been trying to solve this now for around 8 hours and i.m stuck

View 2 Replies
View Related
Aug 1, 2013

I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.

This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.

View 3 Replies
View Related
Apr 4, 2014

I am using Excel 2010. I am extracting text and data info from a single cell and inserting the info into different cells. Let's say the following formula is entered into cell M28:

=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), "")

This tests cell J28 to see if the text string "IM" is present in that cell.

If it is, then the text "IM" plus the following eight characters become the result in cell M28.

If the text string "IM" is not present in cell J28, I want the contents of cell M28, that is the formula itself, to be deleted from cell M28. EG below:

=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), function here to delete this formula from this cell)

( logical test ) ( value if true ) ( value if false )

View 4 Replies
View Related
Jun 4, 2014

Excel 2010. When the result of the formula in Col. E is less than zero, I need to move the row to another sheet, and then delete the row.

I modified another poster's script as follows:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

' Move rows with negative value in E to Flagged Items sheet

Dim rng As Range

' Set Target Range

Set rng = Target.Parent.Range("E2:E200")

[Code] ........

The script works perfectly if a negative value is manually entered into a cell in Col. E, but not when the result of the formula produces a negative number.

I can't find a way to make the Case Is < 0 act on the resultant value rather than the formula.

Is this possible in the script or is there another way to achieve the desired result?

View 4 Replies
View Related
Aug 27, 2012

I use Excel 10 and i'm on Window 7.

I need a formula to transfer the values in on cell to another designated cell. "AM" should be in the cell under "AM and "PM" should be under the cell marked "PM". If there's nothing in the space where AM or PM is, that should be blank.

View 3 Replies
View Related
Apr 30, 2014

Two formulas which I need in Cells E74 and E75.

Basically the formulas will depend whether the entry in D73 are Yes or No

in cell E74, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF ‘D73’ = ‘Yes’ then E74 = E70

in cell E75, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF D73 = ‘Yes’ then E74 = E70

Excel version 2010

View 4 Replies
View Related
Jun 20, 2014

How do I combine a formula and text in the same cell in excel 2010.

What I am looking for is the sum of a number of cells, followed by text.

e.g. something like =SUM(E4:E50)"/35"

If the sum result of cells E4:E50 was 10, I would be expecting to see visible in the cell '10/35'

How do I write this formula?

View 3 Replies
View Related
Aug 7, 2014

(Excel 2010). I have 3 cell that contain data. What I need is to compare the 3 cell and return the data that has different value into 1 new cell.

I have attach an example : test.xlsx

View 3 Replies
View Related
Aug 7, 2012

The formula I found is close but I need it to count, not sum.

=SUMIFS($C$36:$C$51,$B$36:$B$51,$B36)

Example data: [972 1] [972 2] [972 3] [214 1] [214 1] [765 1] [765 2]

I want the formula to count the different numbers in the second column based on the first column. The results should show, 972 equals 3, 214 equals 1, 765 equals 2.

I am trying to separate this out but it keeps putting this paragraph.

View 3 Replies
View Related
Jul 3, 2014

I am having a terrible time with Excel today. The version I'm using is Excel 2010.

I just want a simple division formula in a cell, formatted to accounting. This should be really easy, but it isn't reacting the normal way.

The simple division is =13588/12

The output in accounting format should be 1,132.33.

Every time I enter this formula into a cell that is pre-formatted to accounting, the result is 1132 1/3. It also erases my formula and replaces it with the value.

View 7 Replies
View Related
Aug 30, 2013

My formula is =IF((C2+E2)>D2,"WIN","LOSE") the new formula if I wanted the cells with "WIN" to be green, and the 'LOSE' cells to be yellow. Excel 2010

View 6 Replies
View Related