Conditional Formatting Cells That Contain Formulas - NO VBA?
Mar 28, 2013
I need to apply conditional formatting to cells in a column that contain formulas, or more accurately cells that do not. I have a spreadsheet with default values in a column based on a lookup function. I need to know if the user overwrites the default and cannot use UDF's or VBA. I don't think it's possible so my alternative is to include a hidden column that does the same lookup and do the conditional format off of a comparison.
View 2 Replies
ADVERTISEMENT
Dec 29, 2008
I have searched for a while but can't seem to find where the original thread is at. Could someone point me in the right direction?
View 9 Replies
View Related
Aug 11, 2012
Is there a way to apply conditional formatting to a range of cells that contains formulas?
View 6 Replies
View Related
Jul 9, 2013
I have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.
Sample: =IF($C$5/$C$295>$R$5,TRUE,FALSE)
I want to copy to $D$5/$D$295>$R$5,TRUE,FALSE) without having to paste the new formula in each time.
View 9 Replies
View Related
Nov 24, 2010
Is it possible to use conditional formatting to highlight cells that use formulas, as opposed to having constants?
I have a sheet that uses formulas to provide a default value, but you can type in a number if you want to replace the default. I want to be able to easily identify which ones use the default formula.
If I use functions like FIND, they look at the result of a formula, and not the formula itself.
I know I can write a UDF that will figure it out but I was wondering if there is some built-in way.
View 7 Replies
View Related
Jan 19, 2014
In my workbook I have several column with dates, these are benchmarks that I need to follow and have my patient's follow. They concern the dates of treatments and of lab work. My last column is Contact Needed and does not contain any formulas or code. Is there a way to change the color of the row to adjust for how close we are to the treatment date or the lab draw date.
I was thinking if I were +/- 7 days from each blood draw the row would be yellow, so I could contact the patient and remind them to get the labs done. If it was over 7 days past scheduled blood draw, the row would be light red, as the urgency to get labs and continue treatment has increased as they are past due. In like manner, if treatment are within 14 days, the row would be yellow so i could call them and set the appointment. If past the treatment date, the row goes red and I have 14 days to get them in or we have to start treatment s all over again.
Book0.5 template.xlsx
View 3 Replies
View Related
Aug 20, 2009
Can you have IF and AND statements in Conditional formatting formulas?
I put this and I get an error: =IF(AND($G5=""($H5=<>"")) I was testing the water for adding and OR statment also. I really want if cell G5 is blank and cell H5 or I5 has text, then G5 should be red.
View 12 Replies
View Related
Jun 23, 2012
How do I create 3 color conditional formatting formulas for the following rules?:
if today is 15 days or greater before the day in the cell then green
if today is between 14 and 7 days before the day in the cell then yellow
if today is within 6 days to zero days before the day in the cell then results
anything is clear
x8:x21
5/10/2012
5/14/2012
5/21/2012
6/11/2012
6/12/2012
6/12/2012
6/12/2012
6/20/2012
6/22/2012
6/23/2012
7/4/2012
7/5/2012
7/8/2012
7/12/2012
View 9 Replies
View Related
Jun 11, 2013
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.
View 1 Replies
View Related
Sep 11, 2007
Lets say I have a block of cells (lets say A1:E5) that all reference the value in the upper left hand cell (A1). Could be a formula by itself or one embedded within a conditional format. I reproduced the formula and formats in the A1:E5 block by using the absolute cell reference $A$1.
I now want to reproduce that entire block (including the referenced cell A1) multiple times on the sheet, but the catch is that each new block must reference the cell in IT'S OWN upper left hand corner. (e.g. a block located at F10:J15 must use as it's reference F10, not A1).
How can I quickly reproduce those blocks? I have done a search and replace to eliminate the $ symbols within the formulas to make them relative, but that doesn't seem to work within conditional formats.
The issue right now is I am trying to apply conditional formats to a sheet I already constructed. I don't want to overight the formulas in the UL corner, so I am trying to copy and paste the conditional formatting only. Can't seem to figure out how.
View 9 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
Mar 13, 2014
Is it possible to record a macro or write VBA to apply conditional formatting to certain columns of cells where there are exceptions/conditions for the different types of conditional formatting? If possible, can those exceptions/conditions be based on a specific type of formula?
See my DATA EXAMPLE picture linked below. On that example, Columns K-Q need conditional formatting but manager wants row 38 to be blue based on the fact that it is using a =SUM() formula. This represents one "part" out of 75 that could be on a given sheet, each "part" has a different number of sub-parts that are used. So I can't just highlight columns K-Q because of the occasional SUM row that needs to be blue.
The conditional formatting that I am currently using in Columns K-Q only apply to K3:Q37 and not to row 38 at all. I currently have conditional formatting on columns K-N, P-Q where when '=ISNUMBER(xx)' returns TRUE is white and when it returns FALSE is light orange. Column O uses '=ISTEXT(xx)' for the same colors. Manager doesn't want to copy paste that formatting and wants it setup as a Macro/VBA.
DATA EXAMPLE - [URL]
RELEVANT INFORMATION
Columns K-Q need conditional formattingColumn K - Formula pasted in only on lines that require the Sales Price to show, will return a number value or error
(=VLOOKUP(B38,'SaleWS'!C:G,5,0).Column L - Formula pasted in every cell in column except L38 is '=IF(Hxx="G",IF(Exx"Description EX",VLOOKUP($Dxx&$Gxx,'PriceWS'!$D:$F,3,0),""),"")'.
L38 has a sum of all above valuesColumn M - Formula pasted in every cell in column except M38 is '=IF(ISNUMBER(Lxx),Ixx*Lxx,"")'.
M38 has a sum of all above valuesColumn N - Formula pasted in every cell in column except N38 is '=IF(Exx="Description EX",VLOOKUP(Dxx,'Material'!A:O,15,0),"")'.
N38 has a sum of all above valuesColumn O - No formulas here, just manual entryColumn P - Formula pasted in every cell in column except P38 is '=IF(H38="P",VLOOKUP($Dxx&$Oxx,'PressWS'!$A:$L,12,0),"")'.
P38 has a sum of all above valuesColumn Q - Formula pasted in every cell in column except Q38 is '=IF(H37="W",VLOOKUP($Dxx,'WeldWS'!$A:$F,6,0),IF(Hxx="T",VLOOKUP($Dxx,'WeldWS'!$A:$F,6,0),""))'.
Q38 has a sum of all above values.Rows where they are using SUM for all the rows above for that part need to be blue
View 2 Replies
View Related
Jan 28, 2011
I have an excel file which consist of 12 sheets. The main sheet contain columns from A to EL and 556 rows.
At the moment, i have data on from column A to AI (we will add more data day by day). All data in main sheet are linking to another 4 sheets which basically using IF, VLOOKUP, and Conditional Formatting. My file originally was only 4.6MB in size.
However, after adding the formula (IF & Vlookup) and conditional formatting to the fourth sheet (number of columns is from A to BZ) then the file size increased from 4.6MB to 13.7MB.
My excel file also became very slow and i need to take off the Automatically Calculation option.
View 14 Replies
View Related
Sep 16, 2013
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
View 2 Replies
View Related
Jul 25, 2009
I am trying to adjust a simple formula that I am using:
=IF(D3<>"",1,"") **Note this is clunky, but ISBLANK doesn't seem to work due to D3 containing a formula**
to also change the fill color of A3 to "Red" if data exists in D3. Everywhere I look online for the formula reference tries to point me to Conditional Formatting, Which doesnt seem to apply to seperate cells so I can't use this.
View 3 Replies
View Related
Dec 18, 2007
i am trying to write a formula that calculates the difference between two cells, i.e. the buy price and sell price of a stock, to result in the profit/loss of the trade.
However, the problem is, When "going long" i need to calculate b-a = c
C:C=B:B-A:A
but when i go "short" i need the calculation to be a-b = c
C:C=A:A-B:B
i am thinking that if i have a column, say d, indicating either "long" or "short", as a conditional argument, then this can determine which formula to apply to the calculation...
e.g. please try to make sense of this...
if "d=long" then C:C=B:B-A:A, but if "d=short" then C:C=A:A-B:B
is this sort of calculation possible? any suggestions would be greatly appreciated. i have just finished reading a formula text book, but if the argument im creating isn't exactly shown word for word i am having trouble adapting the formula as needed.
View 9 Replies
View Related
Oct 19, 2009
I want to colour 10 cells (A1-J1) if I type in yes in cell K1. Using conditional formatting Im only able to colour 1 cell. Is this possible without using VBA?
View 5 Replies
View Related
Dec 7, 2006
I have a list of values in a named range called Categories. I use this list in a validation dropdown.
Can I do a CSE formula in the "formula is" field of the conditional formatting dialog such that if one of the values in the list is not chosen, I can color the cell?
I can do a CSE formula to test a list in a cell:
{=if(A1Categories, 1,0)}
This evaluates correctly. I am wondering if there is a way to make this work inside the conditional formatting window. I am trying to avoid the built in error validation msg box options and would love to use this. It's the most efficient way I can think of. I know I can do it by adding another column (and step) and then formatting on the value in there that has the CSE formula, but I thought I'd see if I could learn something new and if this was possible.
View 9 Replies
View Related
Feb 9, 2009
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies
View Related
Nov 17, 2011
Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
View 5 Replies
View Related
Jul 18, 2006
Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??
View 5 Replies
View Related
May 3, 2013
What I am looking to do is;
If cell A2 = "Closed"
then I want cells B2 and C2 to strike through its own text.
I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah
but if
A2 = Closed
then B2 and C2 = Blah blah blah
View 5 Replies
View Related
May 29, 2013
Col A - budget items (description)
Col B - budget dollars
Col C - actual dollars
Row 10 - summation Cols B and C
When the budget is prepared the actual dollars in Col C are equal to the budget dollars for all items. For instance the formula =b2 is placed in cell C2. As time passes the user records actual dollars in Col C for each item by entering the actual amount directly into the cell. Actual dollars do not become known all at the same time, so that Col C will contain a mixture of formulas and entered numbers.
How can I highlight the cells in Col C that have entered numbers rather than formulas.
View 1 Replies
View Related
Jan 17, 2013
I have attached the sample. I need the cells without the employee or without a boss to highlight a color and i also need the date of certification to highlight if it is more than one year old.
conditional formatting.xlsx
View 4 Replies
View Related
Jul 3, 2009
I want to add some colour conditional formatting to the cells in column C, dependent on their variance to the figure in column B. I've got the basic gist of applying the rule to the one single cell, but I'm not sure how to apply it to all the cells in the column. I have tried "dragging" it down, but it then just gives me the variance to the original row.
View 2 Replies
View Related
Apr 15, 2013
i want to know is there any way to set up conditional format to the cells used in a formula which is in another cell.
View 8 Replies
View Related
Jul 24, 2014
I'm using conditional formatting where one column of cells is referencing another column of cells. For example, I want cell K2 to highlight if the date entered doesn't match the date in F2. I then want cell K3 to highlight if the date entered doesn't match the date in F3. I want cell K4 to highlight if the date entered doesn't match the date in F4 and so on. I'm not having any issue putting conditional formatting on the cells/row references individually, but when I try to copy the formatting (by using format painter) to save time all of the cells in column K just reference the first cell in column F since the first cell in column K references that F cell but not any other F cell. How do I copy these row by row references between columns so that each reference is unique?
View 1 Replies
View Related
Aug 18, 2014
On the attached worksheet I'm looking for the squares in F4:F6 to be coloured corresponding to the colour names in D4:D6 using conditional formatting. I just can't get it to work today.
View 1 Replies
View Related
Jan 27, 2014
On the attached spreadsheet, is there a way of highlighting a cell because it is a greater number than two other cells?
Can cell C3 be highlighted green because the number is greater than G3 and K3?
Can cell E3 be highlighted green because the number is greater than A3 and I3?
Can cell K4 be highlighted green because the number is greater than C4 and G4?
View 8 Replies
View Related
Mar 30, 2014
I have a worksheet which has the years 2001 to 2013 on in row1. I am trying to find a way of highlighting the entire row in green if a specific year contains an "a" (Marlett, tick). The complex part is that I would like the specific year to be determined by an entry input into cell A2 on sheet 2. See Attachment for reference.
View 2 Replies
View Related
