Conditional Formatting Based On A Value In A Different Cell?
Apr 9, 2014
I am working in a table where I would like the whole row to highlight based on the value in one of the cells. These rows of data will be updated regularly, so the formatting has to change with the data.
In Excel, I want to do conditional formatting on a cell based on the value of another cell. For example: in cell A1, I have text. I want this cell to have a grey background if the value of cell B1 is less than or equal to 1/31/14 and to have no fill (default) if it is greater than that date.
I want to format a cell conditional on the value of another cell. I have read numerous threads on the topic and no matter what I do, it doesn't seem to work for me. I've made it a simple as possible by creating a new workbook and entering:
A1: 5 B1: 2 C1: Some random text - this cell has the conditional formula: ="A1>B1"
However, the cell doesn't get formatted. Here is a screenshot and here is the simple workbook.
I am attempting to apply conditional formatting such that cell B3 is green if it is greater than or equal to cell B2, and red if less than cell B2. Then to apply this conditioning across the row of data (i.e. C3 >= C2 green, C3 < C2 red; D3 >= D2 green, D3 < D2 red; E3 >= E2 green, E3 < E2 red ... etc.). Needs to be formatted such that if I change the value in cell B2, the colour of B3 is automatically updated.
I am manually formatting column J depending on what data is entered into column K. If the number entered into the cells in column K are equal to or less than what is in column J, I format the data in the cells in column J with the green you see above. If the data entered in the column K cells are greater than column J, I format it in the red shown above.
So, how can I have Excel do this for me, format column J cells depending upon what is entered into column K cells? The caveat here is that I also don't want any formatting to occur until data is entered into column K.
I would like to be able to change the color of fill in a cell if there are 3 occurrences in cells. Attached is a sample.
In the attachment, if the cells A2, D2, G2 all have a number "1" in them I would like cell K2 to have a color fill for the cell.
The same would be for the other cells, if B2, E2, H2 all have the number "1", K2 should have a fill in the cell as well, same as if C2, F2, I2 have a number "1", fill K2.
I only want the fill color to be in cell K2 if all 3 of the cells, ex: A2, D2, G2 have a "1" in them. Also, some of the other cells may have a number "1" in them but I would not want them counted unless it is 3 of the consecutive cells.
I have a cell which I want to colour green if C6>9, yellow if O6>60 and red if BOTH C6 and O6 are above their respective numbers. The first two parts of this are obviously straight forward, but I cant work out how to get the third part of it to work.
I want to do conditional formatting based on two cell values.
i want to do conditional format the column 'R' based on clolumns 'J' and 'R'. column 'J' is a text column named PRIORITY which can hold the values P1,P2,P3 an P4. Column 'R' is nothing but difference of two columns 'M' and 'H'(both are time stamps).Column'R' is in below format. =TEXT(M4-H4,"h:mm:ss").
column M and H are in below format:
eg: 9/11/13 9:41 AM
Now i want to format the column'R' like if column J ="P2" and column 'R' > "00:10:00"(10 mins) =it should become red. if column J="P3" and column 'R'>"00:30:00"(30 mins) = it should become red.
Is it possible to format a cell based upon the format of another cell? For instance, if the cell to the left of a target cell is bold, is there a conditional formatting cell that can be used so that the target cell is bold as well?
I want the font of a value in a cell to turn green or red based on the value of the cell below it - NOT based on a set value or range like all of the other examples are based on. I want to apply a formula or condition to the entire table.
Column a has the date for each value, newest date is row 1 and oldest date is row 250. Column b-k has the number value for each animal species we are tracking. If there are more animals than the day before, the value went up and I want the font to be green. If there are less animals than the day before, then value went down and I want the font to be green.
There are about 250 rows in each table and I have 4 tables. I thought I could do conditional formating but I can only find how to change font color based on a set value. For instance, for each day there are over 10 turkeys, then it would be green. That is NOT what I want, see below:
Mon 5 no color change Fri 5 color change to green Thu 4 color change to red Wed 5 color change to red Tue 7 color change to green Mon 5
date looks more like this - col a is date, col b is turkey, col c deer, etc
I would think this is similar to daily stock prices, if data goes up then it is in green, but if the price goes down then in red. Not based on purchase price (set value), but based on previous days closing price. For this report, I only care about the change day to day.
I conditional format the top number which in this case is 65, how do I highlight the number directly above it? Either by another conditional format or some formula that picks the number out. I've tried HLOOKUP and VLOOKUP, but its giving me spurious results.
Is there a way to shade a cell based on whether the adjacent cell is shaded? I found some ways to do this with VBA but I was looking for a non VBA solution if possible. So basically if cell C5 is highlighted yellow I would want cell D5 highlighted yellow too, regardless of the values inside the cell. Is this possible without VBA?
I would like to use Conditional Formatting to format a cell depending whether it past a certain time, such as 3:45 PM. Naturally, entering the time as the condition does not work. Is there a way to do this?
I am trying to format cells based on what is in them across the board. Is there a way to conditional format based on what is in certain cells? I have 5 columns. I need to color in every NA only in the rows that have 1 or less cells with a number in there. So if there is 2 cells in that one row that have a number in them then leave the whole row white. If there is only 1 number in that row and the rest are NA then color all the NA's in red......
I am using excel 2000. I want to apply conditional formatting to a range of cells if they contain (V) as part of the cell contents. The cells will generally look like this : 09.00-6 (V) or 9-5.30 (V). I want to shade any cell if part of the contents of that cell is (V).
I am working with the Conditional Formatting, which is fine for one cell. Here is what I am trying to do: IF cell in $A1 = 1 then bold $B2:$M2 and apply solid line border to top of cell ranges. I have tried conditional formatting but it only formats the cells in column A. And I can't seem to find a BOLD statement for the cell formulas.
I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:
Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.
i.e. (in reference to attachment - sheet DATA) IF E6 = Holiday, then D6 = Red IF E6 = Half Day Holiday, then D6 = Red IF E6 = Sick, then D6 = Blue IF E6 = Sick Half Day, then D6 = Blue IF E6 = Bank Holiday, then D6 = Green IF E6 = Compassionate Leave, then D6 = Pink IF E6 = Unpaid Leave then, D6 = Yellow IF E6 = Unpaid Leave Half Day then, D6 = Yellow
However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).
I need a macro that will highlight a row based on a cell criteria. I have used conditional formatting but there is a lot to format and has dramatically slowed the sheet down. However, I know nothing about macros/vbas.
E.g. If it states JI sent then I want the whole row to be highlighted light green.
App form needed = peach Waiting list = light blue Cancelled = red font strikethrough DNA = purple font strikethrough.
Also - it could well be that in the row it has App form received, JI Sent and DNA all in 1 go. So ideally I would want that row to be highlighted light green with purple font strikethrough.
Booking Status JI Status Date JI Sent Attendance Cancellation date Chargable / non chargable Cancellations Chargeable / non chargeable DNA Date taken Time taken Event Title Start Date Time
Assume that is the table i have. In row 1 i need conditional formatting such that if any of the columns contain an x the first row should be color coded. In my above example every column in row 1 will be color coded expect for row 1 column 6 since there are no x's for any any row in column 6.
How can i do this in excel.Also instead of x's if i have manually entered color can we do the same ?