Conditional Formatting - Use Info From 2 Columns To Format 3rd Column
May 24, 2014
conditional formatting in Excel. I have two columns with pertinent information. I need to know the following and format accordingly:
1. Is the number in column A positive or negative?
2. Is the number in column B less than 0.05 or 0.10?
I would then like Column C to just be highlighted a certain color depending on the combination... there are 5 possible combinations and I would like the cells to be formatted so that:
1. Positive and less than 0.05 - Bright yellow
2. Positive and less than 0.10 - Pale yellow
3. Negative and less than 0.05 - Bright green
4. Negative and less than 0.10 - Pale green
5. This "combo" just means the criteria wasn't meant... which is possible b/c sometimes Column A may have text instead of a number of b/c the number in column B is not less than 0.10. If either of these is true, I want the cell to remain blank.
I want to format 3 columns in excel depending on certain value from another column. By example: I have the columns A B C completed with some text. On the column D it will be the numbers 0 or 1. If the number is 0 the background color from A,B,C columns shall be Green, if the number is 1 the color shall be red.
I have tried in conditional formatting with the formula =$D$1=0 but the color of the columns are changing only depending on the value from cell D1. I don't know what is the correct formula. For column A1 I want to check the value from D1, A2 - D2, A3 - D3,...,An - Dn.
I need for coloring "Expected Delivery Date" column corresponding with "Control Date" and "Control Result. But I want to correspond with the cells' last content of "Control Date" and "Control Result" But first of all I want "Control Date" should be flashed 10 days ago of it's date... (it's already done on my excel file)
Then I want "Expected Delivery Date" as green when - "Expected Delivery Date" =< "Control Date" and "Control Result" =Y (Means Control is Ok on due time)
I want "Expected Delivery Date" as red when - "Expected Delivery Date" > "Control Date" and "Control Result" =Y (Means control is ok not on due time)
- "Expected Delivery Date" > "Control Date" and "Control Result" =N (Means control ok is not given, delayed...)
However this doesnt work. I tried looking at adding VB script in but to be honest I am not a VB programmer and cant really spend too much time on this. fix the final conditional format so it run if the cell contains either "resolved" or "closed"?
taking a spreadsheet that has vertical repeating info in Column A and results in Column B and converting that to another sheet in a horizontal list. The main problem is that the repeating info in Column A may or may not always be the same for every customer; therefore, when it is placed in a horizontal format some cells may or may not have results. I tried a arbitrary lookup. Here is the formula I used:
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue If AI3=D3 & D3 is red, format AI3 blue Otherwise, leave AI3 unformatted.
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.
In conditional format I've this formula =countif($Q$2:$V$2,B2)>0 which will return any matching numbers in yellow. My question is can I insert that once that row reaches 10 matching numbers the row turns green.
I have a range A1:A20. Each cell in this range contains a number between 1 and 100. I'd like to be able to format this range so that the top 3 or largest 3 numbers appear in 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 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.
I would like to change the number format of a cell in function of the value of another cell. For example, if A1=1, then I would like the cell A2 to be displayed as a percentage. If A1=2, then would like the cell A2 to be displayed as a number.
I have a worksheet with conditional formatting to shade (with the color grey) every nth column using the =MOD(COLUMN(),5)=0 function. I need to manually add cells formatted with various other colors to my worksheet. Some of the colored cells overlay the conditionally formatted shaded columns and the shading shows rather than the colors. I want colors to show on top of the conditional shading.
I've got a problem with organizing my data. I've performed a study with several participants, each of which does several trials with 8 conditions (1,2,3,4,5,6,7, or 8), with each participant doing a condition more than once. I also have a separate column telling me whether they responded correctly or incorrectly (with a 1 or a 0).
I need to find a way to produce a new column to identify whether they got each condition (of the conditions 1,2,3,4,5,6,7, or 8) correct separately, i.e one column for responses to condition 1, one for condition 2 and so on....
It would also be useful if there was a way that once this is done I could summarize their accuracy of responses to each condition.
I've attached an example of my data. excel problem example.xlsx‎
Whoever created my customer contacts Excel sheet prior to my arrival entered all of the contacts address information into one cell and in order for me to put this info into Access, I need them seperated. For example:
10000 X Street Louisville, KY 40291 is in cell E2
I would like it to read:
10000 X Street in cell E2 Louisville in cell F2 KY in cell G2 40291 in cell H2
They also did this with phone numbers (ie. desk# / cell# / fax#).
There are over 1000 contacts in this sheet, so it would take forever to split these apart row by row.
As a pert of a spreadsheet I have 2 columns (M5:N400) of 400 cells deep. Each cell in these columns contains a formula that counts days, when appropriate. They can end up showing a value of blank, a number between 0 and 5, or any number greater than 5. (But very rarely greater than 30)
If the number is greater than 5 then it means that it has exceeded it's target time and I wish to highlight this with conditional formatting (CF). I have a CF on all cells in both columns that says.
If "The cell value" is "Greater than" "5"... then format the background colour to a pale yellow.
It works, but doesn't work.
If the cell value is 0 to 5 it's unchanged. Good. If the cell value is >5 it changes. Good. BUT, if the cell is blank it also changes. Bad!!!
I assume it is because it is looking at the fact that there is a formula in the cell, even though the displayed value is "". So I tried a second level saying that if the cell is equal to "" then no format, but it still changes.
I´m having aproblem with Excel 2007 about Conditional Formatting. I have a row of Dates for example 02-01-2009 03-01-2009 04-01-2009 , etc in different columns.
Then what I want to do is use Conditional Formatting to Format cells on several rows below according to the day (if its weekend paint red, if not, dont do anything). I'm using the "Use a formula to determine which cells to format" and the condition is (supposing the cells with dates are A1 to C1) =WEEKDAY(A1:C1) > 5 .
So with those 3 dates provided lets suppose Januar2nd is not a weekday, so the outputIwant is: Red White White, (Next Row) Red White White, etc for several rows.
Now what I think is not right is it only works for the row in which I have the cursor so its like: (Lets imagine I selected 3 rows on which I want to see that output) Red White White (The row in which the cursor is works fine), but the next row goes Red Red Red and the third the same.
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.
When I hit the macro code you see below I get both columns D and E, starting from row 18, to get 'filled down' to the specified spot. Every second row has a conditional format (when a value is entered in column A) to change the row to the color grey, and every row between it has a conditional format (when any value is entered in column A as well) to have the row changed to the color red. The issue here is that the Macro code messes up the conditional formatting and uses the conditional formatting of those two cells, which are being dragged down, for those entire filled-down columns! This is what I am starting off with test1.xlsm and this is what I end up with using the macro code below (or doing it manually)
test2.xlsm
Is there a way for the Macro code to bypass this issue?
My conditional formatting code works fine for one column; however, when I try to apply the conditional formatting to two columns, the code only applies to the first column listed. Additionally, I have two sets of "rules" that apply to the same column. One of the "rules" includes two columns. The other rule applies to only one column. I'm not sure if this is my issue, or whether I'm trying to apply the same code to two different columns.
I have ammended the colours to 4 colours that I need for a risk assessment tool . I have added a second worksheet and put a dummy sample of my table there. Can you help me with how to put two colums of conditional formatting in the sheet? The risk rating before and that after the risk treatment? Many thanks in anticipation. I have highlighted in yellow the columns where the product of the consequence and likelihood is likely to be and this is where the colour coding needs to be applied. if you can help with putting the solution in the spread sheet that would be a great help. Any reply via this forum or via my email would be greatly appreciated.
I am new and I hope I have not made a mistake in starting a new thread. Sorry if I have but I wanted to bring this up as a new post so I get a sooner response.
